CODEWITHSUNDEEP
sqllinq
kpolewaczyk
kpolewaczyk

Reputation: 75

Why does this Linq Query Return Different Results than SQL Equivalent?

I'm sure I'm missing something simple but I have a linq query here:

 public static List<Guid> GetAudience()
    {
        var createdOn = new DateTime(2018, 6, 30, 0, 0, 0);
        var x = new List<Guid>();

        try
        {
            var query = from acc in Account
                where acc.num != null
                      && acc.StateCode.Equals(0)
                      && acc.CreatedOn < createdOn
                select new
                {
                    acc.Id
                };

            foreach (var z in query)
            {
                if (z.Id != null)
                {
                    x.Add(z.Id.Value);
                }
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
        }
        return x;
    }

I wanted to verify the count in SQL because it would only take a couple seconds so: 

select count(*)
from Account a
where a.num is not null
and a.statecode = 0
and a.createdon < '2018-06-30 00:00:00'

And now the SQL query is returning 9,329 whereas Linq is returning 10,928. Why are my counts so far off when the queries are doing the same thing (so I thought)? What simple thing am I missing?

Thanks in advance--

Upvotes: 0

Views: 40

Answers (1)

Richardissimo
Richardissimo

Reputation: 5765

Your method is returning a list of records where the Id values are not null (plus the other criteria). The SQL query is returning a count of the number of records (plus the other criteria). Without the definition of your table, it's hard to know whether that is significant.

Unrelated tip: it's not a good idea to catch and swallow exceptions like that - the caller of your method will have no idea that anything went wrong, so processing will continue; but it will be using incomplete data, potentially leading to other problems in your program later.

Upvotes: 1

Related Questions