EF6 LINQ cast column

Question

My entity has a field called Value. This field is a string, but it hold int and DateTime values also.

My regular query is something like

SELECT * FROM tableA 
WHERE entityType=@eID and CAST(value as int/datetime/varchar)==@value

How can I make EF generate a query with a cast?

Edit: create suggestion on uservoice

Can you say how you should cast value based on `entityType`? — teo van kot, Jan 16 '15 at 18:26
@teovankot entityType is another field that tells me if I should cast to int, datetime or varchar... ID=1 means int, 2 means datetime and 0 string... — Leonardo, Jan 16 '15 at 18:36
You should absolutely do this the other way around: cast `@value` to the required type. Applying functions to database fields before comparing them impairs indexes. — Gert Arnold, Jan 16 '15 at 20:48
What's the point of a DateTimeOffset field then if you cannot cast it to datetime before comparing it against a local date? — Triynko, Dec 14 '16 at 16:58

score -1 · Answer 1 · answered Jan 16 '15 at 18:50

-1

Here is example:

        int eId = 1;
        int valueInt = 1;
        DateTime valueDateTime = DateTime.Now;
        string valueString = "Test";

        TableA result;
        switch (eId)
        {
            case 1: result = context.tableA.Where(x => x.entityType == eId && Convert.ToInt32(x.value) == valueInt); break;
            case 2: result = context.tableA.Where(x => x.entityType == eId && Convert.ToDateTime(x.value) == valueDateTime); break;
            case 3: result = context.tableA.Where(x => x.entityType == eId && x.value == valueString); break;
        }

answered Jan 16 '15 at 18:50

teo van kot

12,350
10
38
70

did you even test that before posting it as answer? – Leonardo Jan 19 '15 at 18:40
No, but linq can translate convert functions to sql right way – teo van kot Jan 19 '15 at 18:42
really? can you point any kind of source that backs that claim? i`ve been looking for a long time... no luck so far... – Leonardo Jan 19 '15 at 19:25
Here is [msdn list of methods](http://msdn.microsoft.com/en-us/library/bb882655.aspx) that linq to sql doesn't support. `toInt32` and `toDateTime` not listed there so i belive they should work :) but be carefull with to date time i beliave that convertion can be influed by current `collation` on sql server, but you always can see what's happened in profiler :) – teo van kot Jan 19 '15 at 19:31
1

Convert.ToInt32 is definitely not supported. – Roberto Bonini Aug 11 '15 at 17:14
@teo-van-kot - I just tried it my end and I didn't work. – Roberto Bonini Aug 20 '15 at 14:10
@RobertoBonini you make a mistake somewhere here is [msdn list of what Convert Function DOES NOT SUPPORT](https://msdn.microsoft.com/en-us/library/bb882655(v=vs.110).aspx) all others functions have SQL translation and working. – teo van kot Aug 20 '15 at 14:20

score -1 · Accepted Answer · answered Apr 27 '17 at 21:53

-1

It was implemented and completed by the EF team. Check the user invoice link listed on the question

answered Apr 27 '17 at 21:53

Leonardo

10,737
10
62
155

EF6 LINQ cast column

2 Answers2