CODEWITHSUNDEEP
c#sqllinqlinq-to-sql
Adrian Hedley
Adrian Hedley

Reputation: 1569

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.

Upvotes: 1

Views: 320

Answers (2)

Bala R
Bala R

Reputation: 108937

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};

Upvotes: 2

Tim
Tim

Reputation: 15227

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
            };

Upvotes: 3

Related Questions