locked
How to write equivalen code with EF linq instead of sql RRS feed

  • 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

    Wednesday, February 22, 2017 11:17 AM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 22, 2017 1:28 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 23, 2017 8:18 AM

All replies

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 22, 2017 1:28 PM
  • 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?

    Wednesday, February 22, 2017 6:53 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 23, 2017 8:18 AM
  • 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).

    Thursday, February 23, 2017 8:30 AM