Converting Entity Framework query with join and group by to SQL Server query

Question

I having problems converting linq query to a SQL Server query.

var gdevices = (from logs in dbContext.GensetLogs
                group logs by logs.DeviceId into logsgroup
                join devices in dbContext.GensetDevices on logsgroup.FirstOrDefault().DeviceId equals devices.Id
                where devices.RegisteredBy == model.Email || devices.OperatedBy == model.Email || model.StType == "admin"
                select new DeviceRegistrationDTO
                            {
                                PhoneNumber = devices.PhoneNumber,
                                Latitude = devices.Latitude,
                                Longitude = devices.Longitude,
                                LatestRT = logsgroup.Max(d => d.ReadingTime),
                                DeviceName = logsgroup.Max(d => d.ReadingTime).DeviceName,
                                OperatedBy = devices.OperatedBy,
                                ThresholdValue = devices.ThresholdValue
                            }).ToList();

Convert to plain SQL? Hmm, not sure why would you want to do that, but replace `ToList` with `ToString` and there is your SQL query. — Ivan Stoev, Dec 16 '16 at 12:44
Possible duplicate of [How do I view the SQL generated by the Entity Framework?](http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework) — Igor, Dec 16 '16 at 12:55

score 0 · Answer 1 · answered Dec 16 '16 at 12:56

0

If you are trying to convert from Linq to sql queries then use as following

var blogs = context.Blogs.SqlQuery("SELECT * FROM dbo.Blogs").ToList();

Ref:

https://msdn.microsoft.com/en-us/library/jj592907(v=vs.113).aspx

answered Dec 16 '16 at 12:56

Meer

656
9
18

score 0 · Answer 2 · answered Dec 19 '16 at 19:06

i done this in very simple way.

    SELECT  (t.[TransId])
    , t.[SGCode]
    , t.PurchaseDate
    , t.SoldTo
        ,t.Cost
    ,(select top 1 dr.Rate from DepreciationRate dr where t.Assets_TransId = dr.Assets_TransId order by dr.DepDate desc) Rate
    ,(select sc.Name from AssetsSubClass sc where t.SubClass_TransId = sc.TransId) Name

FROM AssetsTransctions t

Converting Entity Framework query with join and group by to SQL Server query

2 Answers2