This is a migrated thread and some comments may be shown as answers.

[Solved] FirstOrDefault options

4 Answers 264 Views

LINQ (LINQ specific questions)

This is a migrated thread and some comments may be shown as answers.

This question is locked. New answers and comments are not allowed.

Art Kedzierski asked on 23 May 2013, 04:04 PM

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.

4 Answers, 1 is accepted

answered on 24 May 2013, 04:08 PM

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.

answered on 28 May 2013, 09:31 AM

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.

answered on 28 May 2013, 01:38 PM

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