CODEWITHSUNDEEP
linqsqlite
ahnkle
ahnkle

Reputation: 477

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

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

Upvotes: 1

Views: 3725

Answers (2)

Muhammad Adeel Zahid
Muhammad Adeel Zahid

Reputation: 17784

You didn't mention if you are using Linq to SQL or linq to entities. However following query should get you 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.

Upvotes: 2

PinnyM
PinnyM

Reputation: 35531

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.

Upvotes: 0

Related Questions