FirstOrDefault options

5 posts, 0 answers
  1. Art Kedzierski
    Art Kedzierski avatar
    107 posts
    Member since:
    Nov 2009

    Posted 23 May 2013 Link to this post

    I understand that ORM has a few limitations when generating SQL at the moment, but I can't seem to decipher from the forums the correct way to re-write my LINQ query to return distinct items, in this case distinct Components. The following throws a "FirstOrDefault on database server side not implemented" error.

    public IQueryable<ProductTree> GetComponentsByProduct(string prod)
            {
                var query = this.DataContext.ProductTrees
                                .Where(w => w.Product == prod)
                                .OrderBy(o => o.Component)
                                .GroupBy(g => g.Component)
                                .Select(x => x.FirstOrDefault());
                return query;
            }

    If I instead use .SelectMany(x => x) then I get repeated values.
  2. Art Kedzierski
    Art Kedzierski avatar
    107 posts
    Member since:
    Nov 2009

    Posted 24 May 2013 Link to this post

    If it were SQL, I would write something like:

    SELECT [Component]
    FROM [dbo].[ProductTree]
    WHERE [Product] = @prod
    GROUP BY [Component]
    ORDER BY [Component]
     
    -- or
     
    SELECT DISTINCT [Component]
    FROM [dbo].[ProductTree]
    WHERE [Product] = @prod
    ORDER BY [Component]

    And I'm thinking I don't really want to return an IQueryable<ProductTree> but rather a simple list (List or IList?) of Components. That still doesn't help me figure out how to generate a list of distinct Component strings(?) from ProductTree objects.
  3. DevCraft banner
  4. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 28 May 2013 Link to this post

    Hello Art,

    The error you experience is caused by the usage of the FirstOrDefault() extension method in the lambda expression of the Select() extension method. Both of those methods are designed to be applied over collections of objects and in the particular case, FirstOrDefault is used over a single object. The necessary result can be achieved like in the following example:
    var query = from p in this.DataContext.ProductTrees
           where p.Product == prod
           orderby p.Component ascending
           group p by p.Component into pComp
           select pComp.Key;
    Or:
    var query = this.DataContext.ProductTrees
             .Where(w => w.Product == prod)
             .OrderBy(o => o.Component)
             .GroupBy(g => g.Component)
             .Select(x => x.Key);
    In this case after filtering, ordering and grouping the objects in the necessary manner, you are selecting only the values of the grouping criteria. Since those values are used as keys for the grouping of the ProductTree objects, they fulfill the requirement to be distinct form one another.

    With Telerik OpenAccess ORM those two queries produce an SQL statement the same as the first one you mention:
    SELECT [Component]
    FROM [dbo].[ProductTree]
    WHERE [Product] = @prod
    GROUP BY [Component]
    ORDER BY [Component]

    Regarding the return value of the GetComponentByProduct() method, the best solution is indeed to use a collection of the string type. 

    Additionally, I would suggest that you take a look at this article where you can find detailed information about the extension methods in general, in this article where the lambda expressions are described and in this article for information about the FirstOrDefault method.

    I hope you find that feasible. If you experience troubles with the suggested solution or have additional questions, do not hesitate to get back to us.  


    Regards,
    Doroteya
    Telerik
    OpenAccess Samples Kit boasts 50+ sample applications providing diverse real-life business solutions. Click to read more and see OpenAccess ORM in action.
  5. Art Kedzierski
    Art Kedzierski avatar
    107 posts
    Member since:
    Nov 2009

    Posted 28 May 2013 Link to this post

    Thanks for those links. So now I have two methods in my extended Domain Service:

    public IQueryable<string> GetComponentsByProduct(string prod)
    {
        return this.DataContext.ProductTrees
              .Where(w => w.Product == prod)
              .OrderBy(o => o.Component)
              .GroupBy(g => g.Component)
              .Select(x => x.Key);
    }
     
    public IQueryable<string> GetVersionsByProdComp(string prod, string comp)
    {
        return this.DataContext.ProductTrees
              .Where(w => w.Product == prod && w.Component == comp)
              .OrderBy(o => o.FP)
              .GroupBy(g => g.FP)
              .Select(x => x.Key);
    }

    Those are accessed via RadDomainDataSources the XAML (I think the sorting is redundant now):

    <telerik:RadDomainDataSource x:Name="rdds_Components"
                      AutoLoad="True"
                      QueryName="GetComponentsByProductQuery">
        <telerik:RadDomainDataSource.DomainContext>
            <Web:GlobalLabManagerDomainContext/>
        </telerik:RadDomainDataSource.DomainContext>
        <telerik:RadDomainDataSource.QueryParameters>
            <telerik:QueryParameter ParameterName="prod"
                           Value="{Binding ElementName=rdds_Resources, Path=DataView.CurrentItem.Product}"/>
        </telerik:RadDomainDataSource.QueryParameters>
        <telerik:RadDomainDataSource.SortDescriptors>
            <telerik:SortDescriptor Member="Name"
                           SortDirection="Ascending"/>
        </telerik:RadDomainDataSource.SortDescriptors>
    </telerik:RadDomainDataSource>
     
    <telerik:RadDomainDataSource x:Name="rdds_Versions"
                      AutoLoad="True"
                      QueryName="GetVersionsByProdCompQuery">
        <telerik:RadDomainDataSource.DomainContext>
            <Web:GlobalLabManagerDomainContext/>
        </telerik:RadDomainDataSource.DomainContext>
        <telerik:RadDomainDataSource.QueryParameters>
            <telerik:QueryParameter ParameterName="prod"
                           Value="{Binding ElementName=rdds_Resources, Path=DataView.CurrentItem.Product}"/>
            <telerik:QueryParameter ParameterName="comp"
                           Value="{Binding ElementName=rdds_Resources, Path=DataView.CurrentItem.Component}"/>
        </telerik:RadDomainDataSource.QueryParameters>
        <telerik:RadDomainDataSource.SortDescriptors>
            <telerik:SortDescriptor Member="Name"
                          SortDirection="Ascending"/>
        </telerik:RadDomainDataSource.SortDescriptors>
    </telerik:RadDomainDataSource>

    But I'm having issues binding to the collections. Since it's a string collection, I thought this would work:

    <telerik:DataFormComboBoxField Label="Component:"
                       DataMemberBinding="{Binding Component, Mode=TwoWay}"
                       ItemsSource="{Binding DataView, ElementName=rdds_Components}"
                       SelectedValuePath="{Binding}"
                       DisplayMemberPath="{Binding}"/>
    <telerik:DataFormComboBoxField Label="Version:"
                       DataMemberBinding="{Binding Version, Mode=TwoWay}"
                       ItemsSource="{Binding DataView, ElementName=rdds_Versions}"
                       SelectedValuePath="{Binding}"
                       DisplayMemberPath="{Binding}"/>

    But it's early, I just got back from a three-day holiday, and i'm only on my first cup of coffee. Hopefully I'll figure it out before you get around to correcting me. ;-)
  6. Yoan
    Admin
    Yoan avatar
    1070 posts

    Posted 31 May 2013 Link to this post

    Hi Art,

    Indeed, DataFormComboBoxField is a wrapper around RadComboBox. You can find information about the use of DataFormComboBoxField in this help article.


    Regards,
    Yoan
    Telerik
    OpenAccess Samples Kit boasts 50+ sample applications providing diverse real-life business solutions. Click to read more and see OpenAccess ORM in action.
Back to Top
DevCraft banner