How To Convert Sql Query Into Linq?

How can I convert the following SQL query into linq? This is what is tried SELECT COUNT(DISTINCT SampleDateTime) AS Total FROM dbo.PrecisionArchive WHERE AgencySourceId

Solution 1:

You could use:

var total = dbo.PrecisionArchive.Where(p => p.AgencySourceId == 7)
                                .Where(p => p.EventType == "R" || p.EventType == "ONS")
                                .Select(p => p.SampleDateTime)
                                .Distinct()
                                .Count();

Solution 2:

Something like this should work:

var result= 
    (from x in dbContext.PrecisionArchive
     where x.AgencySource ==7&&
           (x.EventType == "R" || x.EventType == "ONS")
     select x.SampleDateTime)
    .Distinct()
    .Count();

You could also try using Contains to represent the IN clause, but I don't believe this is supported by Linq-to-SQL:

var result = 
    (from x in dbContext.PrecisionArchive
     where x.AgencySource == 7 &&
           new[] { "R", "ONS" }.Contains(x.EventType)
     select x.SampleDateTime)
    .Distinct()
    .Count();