The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

slindee (28) [Avatar] Offline
#1
It appears in VB.Linq that creating a query with only one column.Distinct() returns no rows or columns. In order to get around this, I have coded the following kludge:
Dim query = (From tblChartPosition In dc.tblChartPositions _
Where tblChartPosition.CharterID = CharterID _
AndAlso tblChartPosition.ChartID = ChartID _
Order By tblChartPosition.ChartYear _
Select tblChartPosition.ChartYear, _
ChartYear2 = tblChartPosition.ChartYear).Distinct()

This returns a set of rows, however the Order By clause is not honored either.
Anybody got a way that consistently works with one column?
fabrice.marguerie (224) [Avatar] Offline
#2
Re: Distinct clause problems
I'm not able to reproduce the issue with the single column. For example, the following query against the Northwind database returns two strings, as expected:

[pre]Customers.Where(Function(c) c.City = "Paris").Select(Function(c) c.CompanyName).Distinct()[/pre]
Could you show us your original query?
slindee (28) [Avatar] Offline
#3
Re: Distinct clause problems
Here is the original query:
Dim dt As New DataTable
Try
Dim dc As New dcDataContext
Dim query = (From tblChartPosition In dc.tblChartPositions _
Where tblChartPosition.CharterID = CharterID _
AndAlso tblChartPosition.ChartID = ChartID _
Order By tblChartPosition.ChartYear _
Select tblChartPosition.ChartYear).Distinct()
dt = query.CopyToDataTable
Catch exc As Exception
Call WinExcept.ExceptionHandler.Exception(clinqRockRoll, exc)
dt = Nothing
Finally
End Try
Return dt

I am thinking that it may be a VB implementation only problem, if not me.
fabrice.marguerie (224) [Avatar] Offline
#4
Re: Distinct clause problems
Is the result different if you use the Distinct keyword instead of the Distinct() query operator?

[pre]From tblChartPosition In dc.tblChartPositions _
Where tblChartPosition.CharterID = CharterID _
AndAlso tblChartPosition.ChartID = ChartID _
Order By tblChartPosition.ChartYear _
Select tblChartPosition.ChartYear _
Distinct[/pre]

Maybe the problem comes from CopyToDataTable? Did you try to simply enumerate the results with a For Each loop and ToString()?

Also, did you check what the generated SQL looks like?
jwooley (123) [Avatar] Offline
#5
Re: Distinct clause problems
As another possibility, you may be running into the issue of projecting an IEnumerable(Of String) as compared to an anonymous type containing a single string. When trying to databind to an IEnumerable(Of String), the databinding binds to the length property of string rather than to the actual string. Try changing your query to the following and see if you get different behaviors:

From tblChartPosition In dc.tblChartPositions _
Where tblChartPosition.CharterID = CharterID _
AndAlso tblChartPosition.ChartID = ChartID _
Order By tblChartPosition.ChartYear _
Select New With {.ChartYear = tblChartPosition.ChartYear} _
Distinct

Jim
slindee (28) [Avatar] Offline
#6
Re: Distinct clause problems
I can confirm that .Distinct() behaves differently from .Distinct, the latter being the syntax that returns the desired results. Just at you show in your example.
One problem remains: the sort order is not honored. Any ideas there?
slindee (28) [Avatar] Offline
#7
Re: Distinct clause problems
In case there is no internal Linq solution, here is an ADO.NET workaround:
Dim dt As New DataTable
Dim dv As New DataView
Dim drv As DataRowView
Dim dt2 As New DataTable("Sorted")
Dim dr As DataRow = Nothing

Try
Dim dcx As New dcRockRollDataContext
Dim query = From tblChartPosition In dcx.tblChartPositions _
Where tblChartPosition.CharterID = CharterID _
AndAlso tblChartPosition.ChartID = ChartID _
Order By tblChartPosition.ChartYear _
Select New With {.ChartYear = tblChartPosition.ChartYear} Distinct
dt = query.CopyToDataTable
dv.Table = dt
dv.Sort = "ChartYear"
dt2.Columns.Add("ChartYear", GetType(String))

For Each drv In dv
dr = dt2.NewRow
dr.Item("ChartYear") = drv.Item("ChartYear")
dt2.Rows.Add(dr)
Next

Return dt2
jwooley (123) [Avatar] Offline
#8
Re: Distinct clause problems
Check out the thread at http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=1712059&SiteID=1. Init, Matt Warren indicates that the distict needs to be called before the order by clause.

Jim
slindee (28) [Avatar] Offline
#9
Re: Distinct clause problems
Here are two solutions that work:
Dim query = From tblChartPosition In tblChartPositions _
Where tblChartPosition.CharterID = CharterID _
AndAlso tblChartPosition.ChartID = ChartID _
Select t = New With {.ChartYear = tblChartPosition.ChartYear} _
Distinct _
Order By t.ChartYear

Or, rather than using an anonymous type:

Dim query = From tblChartPosition In tblChartPositions _
Where tblChartPosition.CharterID = CharterID _
AndAlso tblChartPosition.ChartID = ChartID _
Select ChartYear = tblChartPosition.ChartYear _
Distinct _
Order By ChartYear

Thanks to Doug Rothaus (The Visual Basic Team)
fabrice.marguerie (224) [Avatar] Offline
#10
Re: Distinct clause problems
See also here for more information.