0

Basically I'm trying to do this in LINQ to SQL;

SELECT DISTINCT a,b,c FROM table WHERE z=35

I have tried this, (c# code)

(from record in db.table
select new table {
    a = record.a,
    b = record.b,
    c = record.c
}).Where(record => record.z.Equals(35)).Distinct();

But when I remove column z from the table object in that fashion I get the following exception;

Binding error: Member 'table.z' not found in projection.

I can't return field z because it will render my distinct useless. Any help is appreciated, thanks.

Edit:

This is a more comprehensive example that includes the use of PredicateBuilder,

var clause = PredicateBuilder.False<User>();
clause = clause.Or(user => user.z.Equals(35));
foreach (int i in IntegerList) {
    int tmp = i;
    clause = clause.Or(user => user.a.Equals(tmp));
}

var results = (from u in db.Users
               select new User {
                   a = user.a,
                   b = user.b,
                   c = user.c
               }).Where(clause).Distinct();

Edit2:

Many thanks to everyone for the comments and answers, this is the solution I ended up with,

var clause = PredicateBuilder.False<User>();
clause = clause.Or(user => user.z.Equals(35));
foreach (int i in IntegerList) {
    int tmp = i;
    clause = clause.Or(user => user.a.Equals(tmp));
}

var results = (from u in db.Users
               select u)
               .Where(clause)
               .Select(u => new User {
                   a = user.a,
                   b = user.b,
                   c = user.c
               }).Distinct();

The ordering of the Where followed by the Select is vital.

Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
Red Taz
  • 4,159
  • 4
  • 38
  • 60
  • 1
    May I humbly suggest that you rename your data types and properties a bit? The fact that you create a new *record* by writing `new table { … }` seems quite confusing and non-intuitive, especially because there appears to be a property `db.table` that is a collection of `table`-like objects... – stakx - no longer contributing Sep 12 '11 at 09:49
  • I was just trying to change references to be more generic for the example. Can those that downvoted please provide an explanation so that I don't make the same question-asking mistake in future. – Red Taz Sep 12 '11 at 09:58

2 Answers2

5

problem is there because you where clause is outside linq query and you are applying the where clause on the new anonymous datatype thats y it causing error

Suggest you to change you query like 

(from record in db.table
where record.z == 35
select new table {
    a = record.a,
    b = record.b,
    c = record.c
}).Distinct();
Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
  • I see. Is it possible to do this using `PredicateBuilder`? I am building up a chain of clauses that is only known at runtime. – Red Taz Sep 12 '11 at 10:05
  • @Rob2211 - well you can check the post by me : http://pranayamr.blogspot.com/2011/04/dynamic-query-with-linq.html might help you – Pranay Rana Sep 12 '11 at 10:21
  • Thanks, I'm accepting this as the answer since it explains the problem with the originally posted code. – Red Taz Sep 12 '11 at 11:12
2

Can't you just put the WHERE clause in the LINQ?

(from record in db.table
where record.z == 35
select new table {
    a = record.a,
    b = record.b,
    c = record.c
}).Distinct();

Alternatively, if you absolutely had to have it the way you wrote it, use .Select

.Select(r => new { a = r.a, b=r.b, c=r.c }).Distinct();

As shown here LINQ Select Distinct with Anonymous Types, this method will work since it compares all public properties of anonymous types.

Hopefully this helps, unfortunately I have not much experience with LINQ so my answer is limited in expertise.

Community
  • 1
  • 1
Daryl Teo
  • 5,394
  • 1
  • 31
  • 37
  • Thanks Daryl, this answer helped me with my eventual solution. I didn't realise I could do `(from foo in bar select a).Where().Select()` – Red Taz Sep 12 '11 at 11:14