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};
精彩评论