My apologies, Now that I'm back on my laptop which is where I have 2008 installed (didn't have it with me earlier) I'm looking into a solution and where the error is coming from.
Okay lets see what kind of results you get from this or errors you are get. This may end up giving you just a single distinct combination of title, sourceid and itemid. Which from your requirements doesn't look like it will meet what you need. You will
also notice that I am projecting each field in your select into a new type with out declaring the new key word. depending on how your calling the method which contains this query we may have to change things up a bit. But we'll get there.
var x = (from t in dc.table
where t.sourceId = 5
order by t.itemId descending
select itemId= t.itemId, sourceId=t.sourceId, title=t.title).Distinct()
Only way I would know how to do it now then would be to use a stored procedure and then add that SP to your DBML. It will create a simple function that you can then call through LINQ.
The Distinct() function can be supplied with an equality comparer. I'm sure there must be an easier way but here you go:
public class DistinctTitle : IEqualityComparer<SourceType> {
public bool Equals(SourceType x, SourceType y) {
return x.title.Equals(y.title);
}
public int GetHashCode(SourceType obj) {
return obj.title.GetHashCode();
}
}
So you can do this:
var x = (from t in dc.table
where t.sourceId = 5
order by t.itemId descending
select t)
.Distinct(new DistinctTitle())
.Select(t=>new{t.itemId, t.sourceId, t.title });
I haven't tested this code so there could slight mistakes, but this is the basic method. Do further searches for IEqualityComparer if you have problems.
[Edit: I've fixed the formatting which was broken either because I had Javascript off, or because this editor doesn't work in Firefox and just showed a TextArea instead...]
FaithRaven
Member
42 Points
106 Posts
LINQ select distinct
Feb 15, 2008 10:40 PM|LINK
Hello
I have a select like this one:
var x = from t in dc.table
where t.sourceId = 5
order by t.itemId descending
select new { t.itemId, t.sourceId, t.title }
I would select only the records with a unique title and let the other fields like sourceId to have duplicates. How can I do this please ?
Wencui Qian ...
All-Star
56784 Points
5796 Posts
Microsoft
Re: LINQ select distinct
Feb 18, 2008 06:59 AM|LINK
Hi FaithRaven,
From your description, you can achieve that by this way. Please take a look at the following codes( Some unnecessary codes are ignored ):
var sel = from u in ctx.Users select new{
ID = u.accountID,
UN = u.userName,
UN2 = u.userName,
EM = u.email,
EM2 = u.email
};
this.GridView1.DataSource = sel; this.GridView1.DataBind();It works fine on my machine. In additional, you should take care of giving the different names in case you want select the same field.
Hope this helps you!
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework
Diamsorn
Contributor
2119 Points
384 Posts
Re: LINQ select distinct
Feb 18, 2008 04:13 PM|LINK
Give this a shot.
You can refer to this article for more information on working with Distinct in LINQ.
http://blogs.msdn.com/charlie/archive/2006/11/19/linq-farm-group-and-distinct.aspx
var x = from t in dc.table
where t.sourceId = 5
order by t.itemId descending
select new { t.itemId, t.sourceId, t.title.Distinct() }
My Blog
FaithRaven
Member
42 Points
106 Posts
Re: LINQ select distinct
Feb 21, 2008 10:24 PM|LINK
Thanks for your input Diamsorn.
However the piece of code you wrote gives this error:
Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access.
Diamsorn
Contributor
2119 Points
384 Posts
Re: LINQ select distinct
Feb 21, 2008 11:45 PM|LINK
My apologies, Now that I'm back on my laptop which is where I have 2008 installed (didn't have it with me earlier) I'm looking into a solution and where the error is coming from.
My Blog
Diamsorn
Contributor
2119 Points
384 Posts
Re: LINQ select distinct
Feb 21, 2008 11:56 PM|LINK
Okay lets see what kind of results you get from this or errors you are get. This may end up giving you just a single distinct combination of title, sourceid and itemid. Which from your requirements doesn't look like it will meet what you need. You will also notice that I am projecting each field in your select into a new type with out declaring the new key word. depending on how your calling the method which contains this query we may have to change things up a bit. But we'll get there.
var x = (from t in dc.table
where t.sourceId = 5
order by t.itemId descending
select itemId= t.itemId, sourceId=t.sourceId, title=t.title).Distinct()
My Blog
FaithRaven
Member
42 Points
106 Posts
Re: LINQ select distinct
Feb 24, 2008 12:47 AM|LINK
Thanks for your help Diamsorn.
I cannot select more than 1 column without having new { }. I have tried it before and I've tried it now again with the code you paste, no success.
I have ran the code using new { }. It returned the exactly same rows as it returns without using .Distinct(). Strange.
And yes, I want only the one column (the title) to be unique.
Diamsorn
Contributor
2119 Points
384 Posts
Re: LINQ select distinct
Feb 25, 2008 02:11 PM|LINK
Only way I would know how to do it now then would be to use a stored procedure and then add that SP to your DBML. It will create a simple function that you can then call through LINQ.
My Blog
FaithRaven
Member
42 Points
106 Posts
Re: LINQ select distinct
Feb 28, 2008 09:03 PM|LINK
Thats exactly the answer I was afraid of.
Thanks for your help Diamsorn.
randomaccess
Member
58 Points
18 Posts
Re: LINQ select distinct
Apr 04, 2008 02:52 PM|LINK
The Distinct() function can be supplied with an equality comparer. I'm sure there must be an easier way but here you go:
public class DistinctTitle : IEqualityComparer<SourceType> {
public bool Equals(SourceType x, SourceType y) {
return x.title.Equals(y.title);
}
public int GetHashCode(SourceType obj) {
return obj.title.GetHashCode();
}
}
So you can do this:
var x = (from t in dc.table
where t.sourceId = 5
order by t.itemId descending
select t)
.Distinct(new DistinctTitle())
.Select(t=>new{t.itemId, t.sourceId, t.title });
I haven't tested this code so there could slight mistakes, but this is the basic method. Do further searches for IEqualityComparer if you have problems.
[Edit: I've fixed the formatting which was broken either because I had Javascript off, or because this editor doesn't work in Firefox and just showed a TextArea instead...]