CODEWITHSUNDEEP
vb.netlinqlinq-to-sql
Carlos Mendieta
Carlos Mendieta

Reputation: 872

Distinct in LINQ to SQL is not working as expected(at all)

I have an ASPX webpage that contains a combobox and a listbox. When a user makes a selection the listbox is supposed to be populated with distinct years.

The datasource that fuels this is a .dbml that has one table and contains a Date column. Has data like this:

2018-01-16 00:00:00.000
2017-01-06 00:00:00.000
2017-01-06 00:00:00.000
2018-01-11 00:00:00.000
2018-01-11 00:00:00.000
2018-01-11 00:00:00.000
2017-10-03 00:00:00.000
2017-10-03 00:00:00.000
2018-01-03 00:00:00.000

So in the listbox I expect only:

2017
2018

but, instead, get every year.

2018
2017
2017
2018
2018
2018
2017
2017
2018

My LINQ looks like:

Using context As New ACE_Data Context()
    Dim aircraft As String = comboSelectAircraft.Value
    Dim year As Table(Of A_DETAIL_REPO) = context.A_DETAIL_REPOs
    Dim query = From ace In context.GetTable(Of A_DETAIL_REPO)()
                Where ace.Series.Equals(aircraft)
                Select DateAndTime.Year(ace.Date)
                Distinct 
    listYear.DataSource = query 
    listYear.DataBind()
End Using

I'm not sure where this is going on. Any help is appreciated, thanks.

Upvotes: 1

Views: 69

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109119

The part DateAndTime.Year(ace.Date) can't be translated into SQL. In such cases, LINQ-to-SQL auto-switches to client-side evaluation. That is, it executes as much as possible in SQL and then continues in .Net code to do the part that can't be translated.

But here this approach fails, because the part that's executes as SQL is:

From ace In context.GetTable(Of A_DETAIL_REPO)()
Where ace.Series.Equals(aircraft)
Select ace.Date
Distinct

On the result list of that query DateAndTime.Year(ace.Date) is applied client-side. So, in short, you get the year parts of distinct dates, not the distinct year parts of dates.

The solution is to use ace.Date.Year. This can be translated into the SQL DATEPART function, so DISTINCT will be applied to the years.

Upvotes: 5

Russell Jonakin
Russell Jonakin

Reputation: 1996

This should work

Dim query = (From ace In context.GetTable(Of A_DETAIL_REPO)()
            Where ace.Series.Equals(aircraft)
            Select DateAndTime.Year(ace.Date)).Distinct()

This will force your select statment to return an IEnumerable and then you can use the .Distinct() method on the IEnumerable interface.

Upvotes: 1

Related Questions