CODEWITHSUNDEEP
c#sqllinq
JuhaKangas
JuhaKangas

Reputation: 883

Put joined rows into a merged list

I have the LINQ to SQL query below which works fine, except that I would like to get the "items" selected from "table3" to be put into a single entity object, instead what I'm getting is a new entity with the same Id but different item for each row.

What do I need to do to achieve this?

Thanks!

var result =
        from table1 in db.Table1
        join table2 in db.Table2 on table1.fkId equals table2.fkId
        join table3 in db.Table3 on table2.fkAid equals table3.Id into items
        select new Entity
        {
            Id = table1.Id,
            Name = table1.Name,
            Items = items.Select(x => new AEntity { Id = x.Id, Name = x.Name }).ToList()
        };

return result.ToList();

Edit to elaborate:

From the code above I could get, for example, the following result (pseudocode):

{
    Entity
    {
        Id = 1,
        Name = "1",
        Items
        {
            AEntity
            {
                Name = "33"
                Id = 33
            }
        }
    },
    Entity
    {
        Id = 1,
        Name = "1",
        Items
        {
            AEntity
            {
                Id = 44,
                Name = "44"
            }
        }
    },
    Entity
    {
        Id = 2,
        Name = "2",
        Items
        {
            AEntity
            {                    
                Id = 55,
                Name = "55"
            }
        }
    }
}

But I want this:

{
    Entity
    {
        Id = 1,
        Name = "1",
        Items
        {
            AEntity
            {
                Id = 33,
                Name = "33"
            },
            AEntity
            {
                Id = 44,
                Name = "44"
            }
        }
    },
    Entity
    {
        Id = 2,
        Name = "2",
        Items
        {
            AEntity
            {                    
                Id = 55,
                Name = "55"
            }
        }
    }
}

I hope that clarifies it a bit.

Upvotes: 1

Views: 122

Answers (1)

Aron
Aron

Reputation: 15772

Once again another person falls victim to "trying to do SQL in Linq". The correct way to do this (in Linq) is...*

var items = context.Entities.Include(x => x.AEntities).ToList();

However if you ARE trying to learn to use an ORM, FOR THE LOVE OF ALL THAT IS HOLY DO NOT USE LINQ2SQL. Its broken, unsupported and out of date.

PLEASE USE Entity Framework.

Now you should google Entity Framework Many to Many or One to Many Associations.

Failing that...

var items = from item1 in db.Table1
            select new 
            {
                Id = table1.Id,
                Name = table1.Name,
                Items = db.Table3
                          .Where(x => db.Table2
                                .Where(t2 => t2.fkId == item1.fkId)
                                .Select(t2 => t2.fkAid)
                          .Contains(x.Id)).ToList()
            };

But that just looks ugly.

*Table 2 looks like the age old pattern of, "Databases can't do Many to Many, so we are going to put in an intermediate table that has no data what-so-ever".

Upvotes: 2

Related Questions