FirstOrDefault options

[Art Kedzierski]

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.

[Art Kedzierski]

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.

[Doroteya]

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

[Art Kedzierski]

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. ;-)

[Yoan]

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