CODEWITHSUNDEEP
asp.netlinqlinq-to-entities
DotnetSparrow
DotnetSparrow

Reputation: 27996

Linq and SQL query comparison

I have this SQL query:

SELECT Sum(ABS([Minimum Installment])) AS SumOfMonthlyPayments FROM tblAccount 
INNER JOIN tblAccountOwner ON tblAccount.[Creditor Registry ID] = tblAccountOwner.
[Creditor Registry ID] AND tblAccount.[Account No] = tblAccountOwner.[Account No] 
WHERE (tblAccountOwner.[Account Owner Registry ID] = 731752693037116688)
 AND (tblAccount.[Account Type] NOT IN 
('CA00', 'CA01', 'CA03', 'CA04', 'CA02', 'PA00', 'PA01', 'PA02', 'PA03', 'PA04'))
AND (DATEDIFF(mm, tblAccount.[State Change Date], GETDATE()) <= 
4 OR tblAccount.[State Change Date] IS NULL)
AND ((tblAccount.[Account Type] IN ('CL10','CL11','PL10','PL11')) OR
CONTAINS(tblAccount.[Account Type], 'Mortgage')) AND (tblAccount.[Account Status ID] <> 999)

I have created a Linq query:

var ownerRegistryId = 731752693037116688;
var excludeTypes = new[]
{
    "CA00", "CA01", "CA03", "CA04", "CA02",
    "PA00", "PA01", "PA02", "PA03", "PA04"
};

var maxStateChangeMonth = 4;
var excludeStatusId = 999;
var includeMortgage = new[] { "CL10", "CL11", "PL10", "PL11" };

var sum = (
    from account in context.Accounts
    from owner in account.AccountOwners
    where owner.AccountOwnerRegistryId == ownerRegistryId
    where !excludeTypes.Contains(account.AccountType)
    where account.StateChangeDate == null ||
       (account.StateChangeDate.Month - DateTime.Now.Month)
            <= maxStateChangeMonth
    where includeMortgage.Contains(account.AccountType) ||
        account.AccountType.Contains("Mortgage")
    where account.AccountStatusId != excludeStatusId
    select account.MinimumInstallment).ToList()
    .Sum(minimumInstallment =>
        Math.Abs((decimal)(minimumInstallment)));

return sum;

Are they equal/same ? I dont have records in db so I cant confirm if they are equal. In SQL there are brackets() but in Linq I didnt use them so is it ok?

Please suggest.

Upvotes: 0

Views: 335

Answers (3)

Devart
Devart

Reputation: 121922

The brackets will be generated by LINQ provider, if necessary.
The simplest way to check if the LINQ query is equal to the initial SQL query is to log it like @Atanas Korchev suggested.
If you are using Entity Framework, however, there is no Log property, but you can try to convert your query to an ObjectQuery, and call the ToTraceString method then:
string sqlQuery = (sum as ObjectQuery).ToTraceString();
UPD. The ToTraceString method needs an ObjectQuery instance for tracing, and the ToList() call already performs materialization, so there is nothing to trace. Here is the updated code:

var sum = (
from account in context.Accounts
from owner in account.AccountOwners
where owner.AccountOwnerRegistryId == ownerRegistryId
where !excludeTypes.Contains(account.AccountType)
where account.StateChangeDate == null ||
   (account.StateChangeDate.Month - DateTime.Now.Month)
        <= maxStateChangeMonth
where includeMortgage.Contains(account.AccountType) ||
    account.AccountType.Contains("Mortgage")
where account.AccountStatusId != excludeStatusId
select account.MinimumInstallment);
string sqlQuery = (sum as ObjectQuery).ToTraceString();

Please note that this code will not perform the actual query, it is usable for testing purposes only. Check out this article if you are interested in ready-for-production logging implementation.

Upvotes: 1

Slauma
Slauma

Reputation: 177133

There can be a performance difference:

The SQL query returns a single number (SELECT Sum...) directly from the database server to the client which executes the query.

In your LINQ query you have a greedy operator (.ToList()) in between:

var sum = (...
    ...
    select account.MinimumInstallment).ToList()
    .Sum(minimumInstallment =>
        Math.Abs((decimal)(minimumInstallment)));

That means that the query on the SQL server does not contain the .Sum operation. The query returns a (potentially long?) list of MinimumInstallments. Then the .Sum operation is performed in memory on the client.

So effectively you switch from LINQ to Entities to LINQ to Objects after .ToList().

BTW: Can you check the last proposal in your previous question here which would avoid .ToList() on this query (if the proposal should work) and would therefore be closer to the SQL statement.

Upvotes: 0

Steven
Steven

Reputation: 172646

It is not possible for us to say anything about this, because you didn't show us the DBML. The actual definition of the mapping between the model and the database is important to be able to see how this executes.

But before you add the DBML to your question: we are not here to do your work, so here are two tips to find out whether they are equal or not:

  1. Insert data in your database and run the queries.
  2. Use a SQL profiler and see what query is executed by your LINQ provider under the covers.

If you have anything more specific to ask, we will be very willing to help.

Upvotes: 1

Related Questions