How to write equivalen code with EF linq instead of sql

Question

User-1223304583 posted

please see the sql and tell me how could i achieve the same with EF and linq

with tmp(plant_date) as
(
   select cast('20170101' as datetime)
   union all
   select plant_date + 1
     from tmp
    where plant_date < '20170120'
)
select plant_date, amt
  from  tmp JOIN withyourtable ON ...
option (maxrecursion 0)

please show me with code sample to achieve it with EF and LINQ query.

thanks

Answer 1

User-821857111 posted

Why don't you simply use Database.SqlQuery method to execute the SQL you posted: https://www.mikesdotnetting.com/article/299/entity-framework-code-first-and-stored-procedures

The article is about stored procedures, but the code is the same for raw SQL.

Answer 2

User-1223304583 posted

can't we do it with left join between two entity in EF.

say i have class MyData which has two field one edate and amount. edate is nullable datetime and amount is decimal.

another entity MyDates will have one field called edate which will return list of dates between two date range and then i will join two entity with EF having left join.

what is your suggestion?

Answer 3

User-832373396 posted

Hi mamoni,

if you need to union the result from the two entities,

I suggest that you could refer to format below:

  (from c in db.MyData select c.Id).Union(from e in db.MyDates where e.Id>1 select e.Id);

if you need to left join them. Please refer to :

 var str=  from a in db.MyData
            join b in db.MyDatas
            on a.Id equals b.Id where b.Id>1
            select new { a.Id, bId= b.strAccessionId };

Best regards,

Jolie

Answer 4

User753101303 posted

Hi,

As a side note in my experience it's best to stop trying porting SQL queries to EF. Try to focus on what you actually want and to write directly the corresponding EF query.

Here you used a CTE but it is not needed stricly speaking. Don't you have a "navigation" property you could use ? In Linq to EF join should be rarely useful ((it means you have no explicit relation in your db between those ttwo tables).