CODEWITHSUNDEEP
linqentity-frameworkt-sql
Mivoat
Mivoat

Reputation: 11

How can I avoid Entity Framework issuing repeated queries?

SQL profiler shows EF issuing the same query up to 8 times from the following LINQ query:

var query = (from wtv in _context.WorkTypeVersions
               join rc in _context.RateCategories on wtv.WTVID equals rc.WTVID
               join rsc in _context.RateSubCategories on rc.RCID equals rsc.RCID
               where (wtv.Composite == (ckCompositePreambles.Checked ? "Y" : "N")
                      && rc.CatCode == catCode)

               select new pRateSubCategory()
                {
                  WTVID = wtv.WTVID,
                  RSCID = rsc.RSCID,
                  WTVersionName = wtv.WTVersionName,
                  SubCatCode = rsc.SubCatCode,
                  PCode = rsc.Preamble.SectionCode,
                  RateCategoryName = rc.RateCategoryName,
                  RateSubCategoryName = rsc.RateSubCategoryName,
                  FullSubCatName = rsc.FullSubCatName,
                  PMBID = rsc.PMBID,
                  Preamble = rsc.Preamble
                });

  dgvRateSubCategories.AutoGenerateColumns = false;
  lblSubSectionCount.Text = "SubSections: " + bsRateSubCategories.Count;
  dgvRateSubCategories.DataSource = bsRateSubCategories;

  bsRateSubCategories.DataSource = query;

The TSQL query (produced up to 8 times, although others are interspersed) is this:

    exec sp_executesql N'SELECT 
    [Project1].[WTVID] AS [WTVID], 
    [Project1].[RSCID] AS [RSCID], 
    [Project1].[WTVersionName] AS [WTVersionName], 
    [Project1].[SubCatCode] AS [SubCatCode], 
    [Project1].[SectionCode] AS [SectionCode], 
    [Project1].[RateCategoryName] AS [RateCategoryName], 
    [Project1].[RateSubCategoryName] AS [RateSubCategoryName], 
    [Project1].[FullSubCatName] AS [FullSubCatName], 
    [Project1].[PMBID] AS [PMBID], 
    [Project1].[PMBID1] AS [PMBID1], 
    [Project1].[PMB_Level] AS [PMB_Level], 
    [Project1].[PMB_Text] AS [PMB_Text], 
    [Project1].[Composite] AS [Composite], 
    [Project1].[PMB_XPS] AS [PMB_XPS]
    FROM ( SELECT 
        [Extent1].[WTVID] AS [WTVID], 
        [Extent1].[WTVersionName] AS [WTVersionName], 
        [Extent2].[RateCategoryName] AS [RateCategoryName], 
        [Extent3].[RSCID] AS [RSCID], 
        [Extent3].[PMBID] AS [PMBID], 
        [Extent3].[RateSubCategoryName] AS [RateSubCategoryName], 
        [Extent3].[SubCatCode] AS [SubCatCode], 
        [Extent3].[FullSubCatName] AS [FullSubCatName], 
        [Extent4].[PMBID] AS [PMBID1], 
        [Extent4].[PMB_Level] AS [PMB_Level], 
        [Extent4].[SectionCode] AS [SectionCode], 
        [Extent4].[Composite] AS [Composite], 
        [Extent4].[PMB_Text] AS [PMB_Text], 
        [Extent4].[PMB_XPS] AS [PMB_XPS]
        FROM    [dbo].[WorkTypeVersions] AS [Extent1]
        INNER JOIN [dbo].[RateCategories] AS [Extent2] ON [Extent1].[WTVID] = [Extent2].[WTVID]
        INNER JOIN [dbo].[RateSubCategories] AS [Extent3] ON [Extent2].[RCID] = [Extent3].[RCID]
        LEFT OUTER JOIN [dbo].[Preambles] AS [Extent4] ON [Extent3].[PMBID] = [Extent4].[PMBID]
        WHERE ([Extent1].[Composite] = (CASE WHEN (@p__linq__0 = 1) THEN N''Y'' ELSE N''N'' END)) AND ([Extent2].[CatCode] = @p__linq__1)
    )  AS [Project1]
    ORDER BY [Project1].[SubCatCode] ASC, [Project1].[WTVersionName] ASC',N'@p__linq__0 bit,@p__linq__1 varchar(8000)',@p__linq__0=0,@p__linq__1='A'

Versions of EF are currently: System.Data.Entity: 4.0.0.0, EntityFramework: 4.3.1

What am I missing here?

Upvotes: 1

Views: 386

Answers (1)

casperOne
casperOne

Reputation: 74530

The problem is where you are setting your data source:

bsRateSubCategories.DataSource = query;

The query variable implements the IQueryable<T> interface which extends the IEnumerable<T> interface (which the DataSource requires). When you bind this to say, a grid, the grid will enumerate through the result multiple times in order to render itself.

However, because you've provided the grid with a data source which implements IQuerable<T>, every time the query is enumerated, it will execute the query against the server, which is probably the reason you're seeing it so many times.

To alleviate this, you should materialize your list before assigning it to the DataSource, either with the ToList extension methods, like so (if it's read-only, ToArray would be fine, but if you need to add new items, then use ToList):

bsRateSubCategories.DataSource = query.ToList();

Of course, you'll have to decide when it's appropriate to reset your DataSource as now the source of the data is completely disconnected from the server.

Upvotes: 3

Related Questions