How to remove column-duplicates from the query result using entity-framework?

Question

On my database table I have

Key    | Value
a      | 1
a      | 2
b      | 11
c      | 1
d      | 2
b      | 3

But I just need to get the items which keys are not duplicates of the previous rows. The desired result should be:

Key    | Value
a      | 1
b      | 11
c      | 1
d      | 2

How could we get the desired result using entity-framework?

Note: we need the first value. Thank you very much.

Answer 1

You should look at either writing a View in the database and mapping your entity to that. Or creating a DefiningQuery in the part of your EDMX (aka the bit that ends up in the SSDL file).

See Tip 34 for more information.

Conceptually both approaches allow you to write a view that excludes the 'duplicate rows'. The difference is just where the view lives.

If you have control over the database - I'd put the view in the database If not you can put the view in your inside the and then map to that.

Hope this helps

Alex

Answer 2

var q = from e in Context.MyTable
        group e by e.Key into g
        select new 
        {
            Key = g.Key,
            Value = g.OrderBy(v => v.Value).FirstOrDefault()
        };