none
DynamicQuery API and Distinct

    Question

  • I'm trying to get the DynamicQuery API at the following address:

    Here   (Also check out this address.)

    To work with the overloaded Distinct method.

    Basically, if you've looked at the package, you can see what I did to get the parameterless Distinct to work with the API

    <Extension()> _
           Public Function Distinct(ByVal source As IQueryable) As IQueryable
                If (source Is Nothing) Then Throw New ArgumentNullException("source")
                Return source.Provider.CreateQuery( _
                    Expression.Call( _
                        GetType(Queryable), "Distinct", _
                        New Type() {source.ElementType}, source.Expression))
            End Function


    This works fine and the generated SQL reflects the Distinct call, so I know it isn't just performing the operation on the collection after it has been loaded into memory.

    Specifically, I use this method as follows:

     Dim searchDataContext = New MyCompanyEntitiesDataContext
            Dim query = searchDataContext.Orders.Where(orderWhereClause, orderParams.ToArray). _
                                                Select("New(OrderID, customerpo, customerid, customer.CustomerName, lineitem.designid, lineitem.design.designtitle, orderdate.datedropdead, orderdate.datetoship, orderdate.dateproductionscheduled)"). _
                                                        Distinct(). _
                                                        OrderBy("orderid")

    lineitem and orderdate are entityrefs of Order, and design is an entityref of lineitem.  The real issue is that orders also has an entityset of lineitems, but when I initially tried the following, it did not work, saying that FirstOrDefault was not a property or member of entityset1'(of Lineitem)

     Dim searchDataContext = New DuboWorksEntitiesDataContext
            Dim query = searchDataContext.Orders.Where(orderWhereClause, orderParams.ToArray). _
                                                Select("New(OrderID, customerpo, customerid, customer.CustomerName, lineitems.firstordefault.designid, lineitems.firstordefault.design.designtitle, orderdate.datedropdead, orderdate.datetoship, orderdate.dateproductionscheduled)"). _
                                                        Distinct(). _
                                                        OrderBy("orderid")

    Basically, this is the query I am trying to replicate, but dynamically (with different numbers and types of conditions in the Where clause):

    Dim result = (From res In searchDataContext.Orders _
                      Where res.OrderID >= 164899 _
                       Select New With {res.OrderID, _
                                        res.CustomerPO, _
                                         res.CustomerID, _
                                       res.Customer.CustomerName, _
                                       .DesignID = If(res.LineItems.FirstOrDefault.DesignID = Nothing, String.Empty, CStr(res.LineItems.FirstOrDefault.DesignID)), _
                                        res.LineItems.FirstOrDefault.Design.DesignTitle, _
                                        res.OrderDate.DateToShip, _
                                        res.OrderDate.DateDropDead}).ToList


    This query, my perfect mate, generates a pretty bit of sql that runs quickly and sends back only the information i need.  I had to toss in the trinary if to deal with the fact that sometimes an order wouldn't have any lineitems associated with it and thus it was trying to set DesignID to null, which is not allowed in my schema.

    After much playing around, I can only imagine that i will be able to replicate this behavior with the Distinct keyword.  So that's why I added the parameterless to the API.  But that still gives me back multiple rows, just none that have the same DesignID.  What I really need is just a single row returned for each order with the "DesignID" of the first lineitem.  For this, I imagine that I need to use the IEqualityComparable Distinct overload with orderID being the element selector.

    But how do I implement that???

    Thanks,
    Jerome

    PS I'm using VB, but I'd appreciate an answer in C# just as much

    Thursday, September 18, 2008 10:39 PM