CODEWITHSUNDEEP
c#linqentity-framework
MyDaftQuestions
MyDaftQuestions

Reputation: 4701

Linq query to orderby

My database has 3 tables. All I want to do is order it by the most recent Date in Table 3

The tables look a little like

Table1

ID    CompanyId
1       1
2       1
3       2

Table2

Id    CompanyColumn
1      Blah
2      Bleh

Table3

Id    CompanyId    Date
1       1          5/1/2015
2       1          8/1/2015
3       1          15/1/2015
4       2          6/1/2015

I am using EF and my object is based upon Table1

So, I can do 

var tab1 = this.Entities.Table1;//I now have a reference to table 1


var result = from t in tab1
             group t by d.Table2.Table3.Select(a => a.Date)
             into g
             select g.FirstOrDefault();

Error message is

The key selector type for the call to the 'GroupBy' method is not comparable in the underlying store provider.

I tried

var result = from t in tab1
             group t by d.CompanyId
             into g
             select g.FirstOrDefault();

result = result.OrderByDescending(a => a.Table2.Table3.Select(b => b.DateTime));

Error message is

DbSortClause expressions must have a type that is order comparable.\r\nParameter name: key

I'm totally lost

Upvotes: 1

Views: 1337

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727067

When you do this

d.Table2.Table3.Select(a => a.Date)

or this

a.Table2.Table3.Select(b => b.DateTime)

you get a whole sequence of dates represented by an IQueryable<Date> in the first case or IQueryable<DateTime> in the second case. You get an error because IQueryable<T> is not IComparable<IQueryable<T>>.

Essentially, the problem is that each expression produces potentially more than one Date or DateTime. If you replace Select with an aggregate function, such as Min or Max, the code would compile:

var result = from t in tab1
         group t by d.Table2.Table3.Min(a => a.Date)
         into g
         select g.FirstOrDefault();

or

result = result.OrderByDescending(a => a.Table2.Table3.Min(b => b.DateTime));

Upvotes: 2

Related Questions