开发者

Convert this SQL Statement to LINQ

i am quite new to linq and i am having some difficulties writing this query efficiently in LINQ.

SELECT ID, UniqueID, RouteHeaderID, RouteObjectState, OriginalRouteUniqueID
FROM  dbo.MeterReadingOrderERPRouteCreateResponses
WHERE (ID NOT IN (SELECT MeterReadingOrderERPRouteCreateResponseID
                  FROM  dbo.Tasks
                  WHERE (TaskType = 'MeterReading')))

any help please? I have tried this solution from: How would you do a "not in" query with LINQ? but was getting the following error: Unable to create a constant value of type 'TaskManager.Models.Task'. Only primitive types ('such as Int32, String, and Guid'开发者_运维百科) are supported in this context.


Just doing this off the top of my head, but maybe this?

var subQuery = from t in Tasks
               where t.TaskType == "MeterReading"
               select t.MeterReadingOrderERPRouteCreateResponseID

var query = from m in MeterReadingOrderERPRouteCreateResponses
            where !subQuery.Contains(m.ID)
            select new
            {
                ID = m.ID,
                UniqueID = m.UniqueID,
                RouteHeaderID = m.RouteHeaderID,
                RouteObjectState = m.RouteObjectState,
                OriginalRouteUniqueID = m.OriginalRouteUniqueID
            };


var meterReadingTasks = from task in context.Tasks
                       where task.TaskType == "MeterReading"
                       select task.MeterReadingOrderERPRouteCreateResponseID;

var results = from m in context.MeterReadingOrderERPRouteCreateResponses
              where !meterReadingTasks.Contains(m.Id)
              select new { m.ID, m.UniqueID, m.RouteHeaderID, m.RouteObjectState, m.OriginalRouteUniqueID};
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜