How do I convert the following functions/expressions from SQL to LINQ to Entities?

Question

Dateadd(minute, TS.duration, TS.datesched)

WHERE...

TS.Duration = int.
TS.datesched = datetime.

RTRIM(ISNULL(TC.CityLocation, '') + ' ' + ISNULL(TC.StateLocation, '')

WHERE...

TC.CityLocation = varchar(50)
TC.StateLocation = varchar(10)

CASE
WHEN country = market 
OR country = 'USA' THEN market 
ELSE country + ' | ' + market 
END AS BroadcastMarket

WHERE...

Country = varchar(100)
Market = varchar(100)

CASE 
WHEN country = 'USA' THEN 0 
ELSE 1 
END AS CountrySort

WHERE...

Country = varchar(100)

Jay · Accepted Answer · 2013-06-21T16:39:11.847

I'm going to try an take a stab at this. Basically what your looking for is a projection.

TcCollection is your data set.

var result = (from i in TcCollection
              select new 
              {
                  Date = EntityFunctions.AddMinutes(i.datesched, i.duration),
                  Location = (i.CityLocation ?? "") + " " + (i.StateLocation ?? ""),
                  BroadcastMarket = 
                      (i.market == i.country || i.country == "USA") ? 
                      i.market : 
                      i.country + " | " + i.market,
                  CountrySort = (i.country == "USA") ? 0 : 1
              });

Thanks to Sandy who provided the date part I missed initially. — Jay, Jun 21 '13 at 16:40

score 1 · Answer 2 · edited May 23 '17 at 11:56

1

You will have to use Entity Functions for date operations like AddDays,AddMinutes in LINQ. Please see this and this

edited May 23 '17 at 11:56

Community

1
1

answered Jun 21 '13 at 16:34

Sandeep Pote

161
8

score 0 · Answer 3 · answered Jun 21 '13 at 16:25

A case in the Select will be done in the projection of the LINQ. You could have a multiline anonymous method so that you could have if/else statements instead, but here I show just using a ternary operator.

CASE 
WHEN country = 'USA' THEN 0 
ELSE 1 
END AS CountrySort

Becomes:

.Select(c=> new { countrySort = (c.country == "USA" ? 0 : 1)});

How do I convert the following functions/expressions from SQL to LINQ to Entities?

3 Answers3