locked
Count(Distinct x) using Dynamic Linq Queries RRS feed

  • Question

  • I've been working with the System.Linq.Dynamic extension to see if it will meet the needs of a B.I. application we're working on.  The application needs to dynamically build almost every aspect of the query at run-time.   I've found that dynamic queries will handle almost everything we need.  One issue I'm stuck on is executing the SQL equivalent of select x, count(distinct y) from tbl group by x.

    Using the standard linq language, I can perform a distinct count with something that looks like this

     

    var qry = from a in db.Orders
    group a by new { a.Member.LastName} into g
    select new { LastName = g.Key, CountDistinctOrders = g.Select(p => p.Order.P).Distinct().Count() };

    This is great, but I'm looking to do this in dynamic linq since I won't know at design time if a Distinct Count will be requested by the user.  Here's where I'm at with the dynamic version

     

    var qry = db.Orders.
    .GroupBy(
    "new(MemberDemographic.LastName)", "it").
    Select(
    "new(Key.LastName as LastName, " +
    "Count() as TotalNotDistinct ");

     

    Any ideas on how to get Distinct Counts of a column?    Thanks!

    Saturday, July 25, 2009 4:01 PM

Answers

  • This doesn't do quite what you want (i.e. count(distinct(field)) ) but if you want to be able to call Distinct on a dynamic linq iqueryable, add the following extension method:

        public static IQueryable Distinct(this IQueryable source)
        {
            if (source == null) throw new ArgumentNullException("source");
            return source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Distinct",
                    new Type[] { source.ElementType },
                    source.Expression));
        }
    

    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Monday, July 27, 2009 3:22 AM
    Answerer