locked
Count on 2 Distinct Columns In Linq To EF - In One Query RRS feed

  • Question

  • I am trying to write a count on 2 distinct columns via Linq to EF to replicate the following SQL statement in EF 5 using .net 4.5 (c#):

    SELECT count(distinct orderId) as totalOrders, count(distint productId) as totalProducts
    FROM products
    WHERE IsActive = 1

    All code I have found in forums or on stack overflow points to syntax that will create 2 separate sub queries.  IE:

    var results = (from p in context.products
    where p.IsActive
    group c by 0 into g
    select new
    {
    OrderCount = g.Select(t => t.orderId).Distinct().Count(),
    ProductCount = g.Count()
    }).FirstOrDefault();

    Which result in something like:

    SELECT 
    [Project3].[C1] AS [C1], 
    [Project3].[C3] AS [C2], 
    [Project3].[C2] AS [C3]
    FROM ( SELECT 
    [Limit1].[C1] AS [C1], 
    [Limit1].[C2] AS [C2], 
    (SELECT 
    COUNT(1) AS [A1]
    FROM ( SELECT DISTINCT 
    [Extent2].[pptyid] AS [pptyid]
    FROM (SELECT 
          ...
          FROM ..
    WHERE ...
    )  AS [Distinct1]) AS [C3]
    FROM ( SELECT TOP (1) 
    @p__linq__0 AS [p__linq__0], 
    @p__linq__1 AS [p__linq__1], 
    @p__linq__2 AS [p__linq__2], 
    @p__linq__3 AS [p__linq__3], 
    @p__linq__4 AS [p__linq__4], 
    [GroupBy1].[K1] AS [C1], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
    [Filter1].[K1] AS [K1], 
    COUNT([Filter1].[A1]) AS [A1]
    FROM ( SELECT 
    0 AS [K1], 
    1 AS [A1]
    FROM (SELECT 
          ...
          FROM ..
    WHERE ...
    )  AS [Filter1]
    GROUP BY [K1]
    )  AS [GroupBy1]
    )  AS [Limit1]
    )  AS [Project3]

    Is there any possible better solution to this without having to use 2 sub queries?  We have noticed as we add parameters to the search (or where clause) the query cost is doubled over the same count(*) on the 2 columns in one query.  Also over a large dataset and the number of times this query is being run with different parameters we view it as a significant issue.

    Thank you for your time and help.


    Tuesday, December 4, 2012 12:47 AM

All replies

  • Hi Randy,

    Welcome to the MSDN forum.

    I recommend you use raw SQL directly: http://msdn.microsoft.com/en-us/data/jj592907.aspx

    If I misunderstood anything, please feel free to let me know.

    Good day!


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, December 5, 2012 7:53 AM
  • Hi Alexander,

    I am to understand then that doing the count on 2 distinct column values is something that is not possible then in Entity Framework without using a raw SQL statement?  We are having our devs use LINQ chaining and would rather have them not use raw SQL to build the queries.  IE:

    search = search.Where(s=>s.OrderType == 3);

    ..

    search = search.Where(s=>s.ProductInStock);

    We will loose all benefits of strong typed object handling if we need to go to writing where statements in plain text, not to mention introduce room for errors and injection (even if we encourage them to parameterize everything...).

    Are there any other options?

    Thanks,

    Randy


    Wednesday, December 5, 2012 11:42 PM