Need help in converting SQL query to EF structure

[Palak Shah]

Hello,

I am new in EF queries, and I have created query in SQL but now I want to covert it in EF structure, Can someone please help me in that?

select top 1 COUNT(ojd.Date) from OrderJobStatusDetails ojd
      where orderid = :orderId and JobStatus = 3 and IsDeleted = 0
      group by ojd.Date
      order by Count(ojd.Date) desc

 

[Skydiver]

Perhaps I am missing something, but it looks like you are getting a scalar value back: the count of orders from the day with the most orders. Why would you need entity framework to hold a scalar value?

 

[Palak Shah]

Perhaps I am missing something, but it looks like you are getting a scalar value back: the count of orders from the day with the most orders. Why would you need entity framework to hold a scalar value?

@Skydiver In the automation framework now SQL repository is not used instead now we need to use EF queries and due to that I need to convert it

 

[Skydiver]

Seems like using a nuke to kill a fly...

Anyway I would think that your EF style query would look something like:

int count = db.OrderJobStatusDetails
                .Where(details => details.OrderId == orderId &&
                                  details.JobStatus == JobStatus.Active &&
                                  !details.IsDeleted)
                .GroupBy(details => details.Date)
                .OrderByDescending(group => group.Count())
                .First()
                .Count();

 

[Palak Shah]

Seems like using a nuke to kill a fly..

haha - not sure why Automation team have opted for EF strcture..But thank you so much for your help

 

[jmcilhinney]

Why would you need entity framework to hold a scalar value?

It's not a case of needing EF to hold a scalar value. If you write your own SQL code then you will likely have all sorts of queries against your database, some of which may be scalar while others are not. If you use EF then that is intended to be the ONLY interface between the app and the database, so you need to write ALL your queries using LINQ to Entities, be they scalar or otherwise. I could be wrong but I doubt that this is the only query being executed against this EF context.

 

[Skydiver]

Interesting point. So code like below should be banned:

var query = $"select top 1 COUNT(ojd.Date) from OrderJobStatusDetails ojd
      where orderid = {orderId} and JobStatus = 3 and IsDeleted = 0
      group by ojd.Date
      order by Count(ojd.Date) desc";
   
var count = context.Database.SqlQuery<int>(query);

 

[jmcilhinney]

Interesting point. So code like below should be banned:

var query = "select top 1 COUNT(ojd.Date) from OrderJobStatusDetails ojd
      where orderid = ${orderId} and JobStatus = 3 and IsDeleted = 0
      group by ojd.Date
      order by Count(ojd.Date) desc";
   
var count = context.Database.SqlQuery<int>(query);

I don't know whether that's a statement or a question. Regardless, people can do whatever they want but, if you're going to use EF then you should use EF. For particular queries, there may be valid reasons to fallback to writing your own Entity SQL, e.g. auto-generated SQL might be particularly inefficient, but using LINQ to Entities should be the default. If you use LINQ to Entities and encounter a particular issue, then you might fall back to Entity SQL.