search

kenwilcox / morelinq

Automatically exported from code.google.com/p/morelinq
Apache License 2.0
0 stars 0 forks source link

DistinctBy does not generate any sql when using against entity framework 4.0 models #71

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
Since there is no sql generated by DistinctBy() all records are returned from 
the database (server) and duplicates are thrown away on the client (web 
server). This is a huge waste of network bandwidth, especially if you have a 
lot of duplicate rows.

It's expected that when using DistinctBy() against an object that is backed by 
a database that the extension method generate sql to find the distinct records 
on the server-side (database) and not the client-side.

Take this example:
List<cmsSearchLog> existingSearchTerms = context.cmsSearchLogs.Where(b => 
b.SearchTerms.ToLower().Contains(terms.ToLower())).Take(maxSuggestions).OrderBy(
o => o.SearchTerms).DistinctBy(d => d.SearchTerms).ToList();

This executes the folowing sql (as shown through sql profiler)
exec sp_executesql N'SELECT 
[Limit1].[SearchId] AS [SearchId], 
[Limit1].[SearchTerms] AS [SearchTerms], 
[Limit1].[MemberId] AS [MemberId], 
[Limit1].[IpAddress] AS [IpAddress], 
[Limit1].[TotalResults] AS [TotalResults]
FROM ( SELECT TOP (10) 
    [Extent1].[SearchId] AS [SearchId], 
    [Extent1].[SearchTerms] AS [SearchTerms], 
    [Extent1].[MemberId] AS [MemberId], 
    [Extent1].[IpAddress] AS [IpAddress], 
    [Extent1].[TotalResults] AS [TotalResults]
    FROM [dbo].[cmsSearchLog] AS [Extent1]
    WHERE ( CAST(CHARINDEX(LOWER(@p__linq__0), LOWER([Extent1].[SearchTerms])) AS int)) > 0
)  AS [Limit1]
ORDER BY [Limit1].[SearchTerms] ASC',N'@p__linq__0 
nvarchar(4000)',@p__linq__0=N'de'

The actual data returned is in the attached csv.

Original issue reported on code.google.com by stranded...@gmail.com on 25 Nov 2011 at 10:50

Attachments:

GoogleCodeExporter commented 9 years ago 
MoreLINQ is not an IQueryable and IQueryProvider implementation. It is 
"Extensions to LINQ to Objects" (i.e. non-standard query operators for LINQ), 
not LINQ to SQL, Entity Framework or what have you.

Original comment by azizatif on 25 May 2012 at 4:56