c# - LINQ query optimization for slow grouping -

c# - LINQ query optimization for slow grouping -

i have linq query gets info via entity framework code first sql database. works, works very slow.

this original query:

var tmpresult = mdv in allmetadatavalues mdv.metadata.inputtype == metadatainputtype.string && mdv.metadata.showinfilter && !mdv.metadata.ishidden && !string.isnullorempty(mdv.valuestring) grouping mdv new { mdv.valuestring, mdv.metadata } g allow first = g.firstordefault() select new { metadatatitle = g.key.metadata.title, metadataid = g.key.metadata.id, collectioncolor = g.key.metadata.collection.color, collectionid = g.key.metadata.collection.id, metadatavaluecount = 0, metadatavaluetitle = g.key.valuestring, metadatavalueid = first.id };

this generated sql original query:

{select 0 [c1], [project4].[title] [title], [project4].[id] [id], [extent9].[color] [color], [project4].[collection_id] [collection_id], [project4].[valuestring] [valuestring], [project4].[c1] [c2] (select [project2].[valuestring] [valuestring], [project2].[id] [id], [project2].[title] [title], [project2].[collection_id] [collection_id], (select top (1) [filter4].[id1] [id] ( select [extent6].[id] [id1], [extent6].[valuestring] [valuestring], [extent7].[collection_id] [collection_id1], [extent8].[id] [id2], [extent8].[inputtype] [inputtype], [extent8].[showinfilter] [showinfilter], [extent8].[ishidden] [ishidden1] [dbo].[metadatavalue] [extent6] left outer bring together [dbo].[media] [extent7] on [extent6].[media_id] = [extent7].[id] inner bring together [dbo].[metadata] [extent8] on [extent6].[metadata_id] = [extent8].[id] ( not (([extent6].[valuestring] null) or (( cast(len([extent6].[valuestring]) int)) = 0))) , ([extent7].[ishidden] <> cast(1 bit)) ) [filter4] (2 = cast( [filter4].[inputtype] int)) , ([filter4].[showinfilter] = 1) , ([filter4].[ishidden1] <> cast(1 bit)) , ([filter4].[collection_id1] = @p__linq__0) , (([project2].[valuestring] = [filter4].[valuestring]) or (([project2].[valuestring] null) , ([filter4].[valuestring] null))) , (([project2].[id] = [filter4].[id2]) or (1 = 0))) [c1] ( select [distinct1].[valuestring] [valuestring], [distinct1].[id] [id], [distinct1].[title] [title], [distinct1].[collection_id] [collection_id] ( select distinct [filter2].[valuestring] [valuestring], [filter2].[id3] [id], [filter2].[inputtype1] [inputtype], [filter2].[title1] [title], [filter2].[showinfilter1] [showinfilter], [filter2].[ishidden2] [ishidden], [filter2].[collection_id2] [collection_id] ( select [filter1].[valuestring], [filter1].[collection_id3], [filter1].[ishidden3], [filter1].[id3], [filter1].[inputtype1], [filter1].[title1], [filter1].[showinfilter1], [filter1].[ishidden2], [filter1].[collection_id2] ( select [extent1].[valuestring] [valuestring], [extent2].[collection_id] [collection_id3], [extent4].[ishidden] [ishidden3], [extent5].[id] [id3], [extent5].[inputtype] [inputtype1], [extent5].[title] [title1], [extent5].[showinfilter] [showinfilter1], [extent5].[ishidden] [ishidden2], [extent5].[collection_id] [collection_id2] [dbo].[metadatavalue] [extent1] left outer bring together [dbo].[media] [extent2] on [extent1].[media_id] = [extent2].[id] inner bring together [dbo].[metadata] [extent3] on [extent1].[metadata_id] = [extent3].[id] left outer bring together [dbo].[metadata] [extent4] on [extent1].[metadata_id] = [extent4].[id] left outer bring together [dbo].[metadata] [extent5] on [extent1].[metadata_id] = [extent5].[id] ( not (([extent1].[valuestring] null) or (( cast(len([extent1].[valuestring]) int)) = 0))) , ([extent2].[ishidden] <> cast(1 bit)) , (2 = cast( [extent3].[inputtype] int)) , ([extent3].[showinfilter] = 1) ) [filter1] [filter1].[ishidden3] <> cast(1 bit) ) [filter2] [filter2].[collection_id3] = @p__linq__0 ) [distinct1] ) [project2] ) [project4] left outer bring together [dbo].[collection] [extent9] on [project4].[collection_id] = [extent9].[id]}

if remove "let first = g.firstordefault()" , alter "metadatavalueid = first.id" "metadatavalueid = 0" have fixed id = 0 testing purposes, info loads fast , generated query half size compared original seems part making query slow:

let first = g.firstordefault() ... metadatavalueid = first.id };

how can rewritten? if seek rewrite code, still slow:

metadatavalueid = g.select(x => x.id).firstordefault()

or

let first = g.select(x => x.id).firstordefault() ... metadatavalueid = first };

any suggestions?

using ef have allways felt has problems efficiently translating stuff g.key.metadata.collection, seek bring together more explicitly , include fields, neccessary result. can utilize include instead of bring together using repository pattern.

then query this:

mdv in allmetadatavalues.include("metadata").include("metadata.collection") mdv.metadata.inputtype == metadatainputtype.string && mdv.metadata.showinfilter && !mdv.metadata.ishidden && !string.isnullorempty(mdv.valuestring) grouping mdv new { metadataid = mdv.metadata.id, collectionid = mdv.metadata.collection.id, mdv.metadata.title, mdv.metadata.collection.color, mdv.valuestring } g allow first = g.firstordefault().id select new { metadatatitle = g.key.title, metadataid = g.key.metadataid, collectioncolor = g.key.color, collectionid = g.key.collectionid, metadatavaluecount = 0, metadatavaluetitle = g.key.valuestring, metadatavalueid = first }

good tool playing linq linqpad.

the problem that:

allow first = g.firstordefault().id

cannot translated sql see this answer. rewrite simplifies underlying query @ least. remains me unclear, why need first id set without using orderby.

it rewriten this:

let first = (from f in allmetadatavalues f.metadata.id == g.key.metadataid && f.valuesstring == g.key.valuesstring select f.id) .firstordefault()

this way not allow ef write query , can specify how select. speed query can consider adding indexes database according generated query - namely index using both colums used in clause of let first query.

c# linq entity-framework repository-pattern