This question is locked. New answers and comments are not allowed.
Art Kedzierski
Top achievements
Rank 2
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.
If I instead use .SelectMany(x => x) then I get repeated values.
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
0
Art Kedzierski
Top achievements
Rank 2
answered on 24 May 2013, 04:08 PM
If it were SQL, I would write something like:
-- or
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.
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.
0
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:
Or:
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:
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
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;
var query =
this
.DataContext.ProductTrees
.Where(w => w.Product == prod)
.OrderBy(o => o.Component)
.GroupBy(g => g.Component)
.Select(x => x.Key);
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.
0
Art Kedzierski
Top achievements
Rank 2
answered on 28 May 2013, 01:38 PM
Thanks for those links. So now I have two methods in my extended Domain Service:
Those are accessed via RadDomainDataSources the XAML (I think the sorting is redundant now):
But I'm having issues binding to the collections. Since it's a string collection, I thought this would work:
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. ;-)
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. ;-)
0
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
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.