Answered by:
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
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