通过JOIN和Group通过SQL Server查询转换实体框架查询

Question

我在将linq查询转换为SQL Server查询时遇到问题。

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();

Answer 1

如果要从linq转换为sql查询，则使用如下

var blogs = context.blogs.sqlquery（" select * from dbo.blogs"）。tolist（）;

参考：

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

Answer 2

我以非常简单的方式完成了此操作。

    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