How do I write a LINQ query to combine multiple rows into one row?

Question

I have one table, 'a', with id and timestamp. Another table, 'b', has N multiple rows referring to id, and each row has 'type', and "some other data".

I want a LINQ query to produce a single row with id, timestamp, and "some other data" x N. Like this:


    1 | 4671 | 46.5 | 56.5

where 46.5 is from one row of 'b', and 56.5 is from another row; both with the same id.

I have a working query in SQLite, but I am new to LINQ. I dont know where to start - I don't think this is a JOIN at all.


    SELECT 
       a.id as id,
       a.seconds,
       COALESCE(
         (SELECT b.some_data FROM
           b WHERE
             b.id=a.id AND b.type=1), '') AS 'data_one',
       COALESCE(
         (SELECT b.some_data FROM
           b WHERE
             b.id=a.id AND b.type=2), '') AS 'data_two'

       FROM a first
       WHERE first.id=1
       GROUP BY first.ID

score 2 · Accepted Answer · answered Feb 24 '12 at 14:56

you didn't mention if you are using Linq to sql or linq to entities. However following query should get u there

(from x in a
join y in b on x.id equals y.id
select new{x.id, x.seconds, y.some_data, y.type}).GroupBy(x=>new{x.id,x.seconds}).
Select(x=>new{
    id = x.key.id,
    seconds = x.Key.seconds,
    data_one = x.Where(z=>z.type == 1).Select(g=>g.some_data).FirstOrDefault(),
    data_two = x.Where(z=>z.type == 2).Select(g=>g.some_data).FirstOrDefault()
});

Obviously, you have to prefix your table names with datacontext or Objectcontext depending upon the underlying provider.

This answer helped me more, but both were useful. However, the LINQ query I wrote isn't working with SQLite atm. Not sure whether it is my LINQ query or the DBLinq driver. — ahnkle, Feb 29 '12 at 13:44

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

What you want to do is similar to pivoting, see Is it possible to Pivot data using LINQ?. The difference here is that you don't really need to aggregate (like a standard pivot), so you'll need to use Max or some similar method that can simulate selecting a single varchar field.

edited May 23 '17 at 10:27

Community

1
1

answered Feb 24 '12 at 14:34

PinnyM

35,165
3
73
81

How do I write a LINQ query to combine multiple rows into one row?

2 Answers2