LINQ to SQL selective choosing of fields from a group

Question

I have a LINQ to SQL query which at one point, returns two rows which I'd like to group by ID and select specific fields, lets say something like this:

ID | Field1 | Field2 | Field3
-----------------------------
1  | 2      | null   | 4
1  | 3      | 5      | null

What's unusual about this query is that from the group, I want to select something like this:

ID | Field1 | Field2 | Field3
-----------------------------
1  | 2      | 5      | 4

Note that the group selects fields from both records. Also note that it's not a simple null coalesce type operation as I need to be able to select a Field1 of 2 or 3 based on some other factors (another field).

Is there a clever way I can project into a new type whilst selecting specific fields as I want them?

I'm working on an IQueryable at this point, so I need a solution which translates into SQL, previously the query did something like this:

.Select(x => new MyObject {
     Field1 = myGroup.First(x => x.Field4 == 1).Field1,
     Field2 = myGroup.FirstOrDefault(x => x.Field4 == 1) == null ? myGroup.FirstOrDefault(x => x.Field4 == 2).Field2 : myGroup.FirstOrDefault(x => x.Field4 == 1).Field2
});

Which doesn't work as it can't work out what to with the operations.

I think we might be resigned to selecting some combination object with all the fields denormalised and then selecting from LINQ to objects in memory, but I'd rather have the DB do the work if possible.

score 1 · Answer 1 · edited May 23 '17 at 12:29

What you need to do is write the C#/LINQ expression to generate a SQL CASE statement.

Generally speaking a case statement is written:

M = CASE 
WHEN A == P THEN V
WHEN B == Q THEN W
WHEN C == R THEN X
WHEN D == S THEN Y
ELSE Z
END

In these examples all the letters A = P to D = S, and V to Z can be any expression of any complexity. So for example you can say WHEN a.cost > 1.5 * b.estimate THEN c.bestoffer.

To generate that exact case statement you need the following LINQ/C#:

M =
(
  A == P? V :
  B == Q? W :
  C == R? X :
  D == S? Y :
  Z
)

So to attack your (unstated) problem directly, you can write arbitrary conditions for the values of each field, using ternary operators and these will be converted to LINQ CASE statements.

See also (and credit to) to the following:

score 1 · Answer 2 · answered Aug 27 '15 at 23:45

If I understand what you're asking for, should be something like this:

from g in (from entity in db.Entities
           group entity by entity.Id)
select new
{
    Id = g.Key,
    Field1 = g.Select(e => e.Field1)
              .FirstOrDefault(f => null != f),
    Field2 = g.Select(e => e.Field2)
              .FirstOrDefault(f => null != f),
    Field3 = g.Select(e => e.Field3)
              .FirstOrDefault(f => null != f),
}

Note that if – for any given Fieldx – there is no non-null value of that field within that group, the resultant Fieldx value will be null.

LINQ to SQL selective choosing of fields from a group

2 Answers2