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

halfer · Accepted Answer · 2023-09-20 22:09:59Z

2

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.

edited Sep 20, 2023 at 22:09

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Feb 24, 2012 at 14:56

Muhammad Adeel Zahid

17.8k16 gold badges95 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ahnkle Over a year ago

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.

Community · Accepted Answer · 2017-05-23 10:27:24Z

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, 2017 at 10:27

CommunityBot

11 silver badge

answered Feb 24, 2012 at 14:34

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related