Entity Framework - Combining data on two rows

Question

I have two tables.

Orders
OrderID | UserID | OrderTotal
1       |    1   |  100
2       |    2   |  110
3       |    1   |  120

Users
UserId | ProprtyType | PropertyValue
1      |     1       |    Kevin
1      |     2       |    Nolan
1      |     1       |    FirstName
1      |     2       |    Surname

Using the following Query

var query = from orders in context.Orders
join users in context.Users on orders.UserID equals user.UserID
where userData.Type == 211 || userData.Type == 212

1       |    1   |  100 | Kevin
1       |    1   |  100 | Nolan
2       |    2   |  110 | FirstName
2       |    2   |  110 | Surname
3       |    1   |  120 | Kevin
3       |    1   |  120 | Nolan

Is it possible in Entity frame work to combine the results so it returns the following

1       |    1   |  100 | Kevin     | Nolan
2       |    2   |  110 | FirstName | Surname
3       |    1   |  120 | Kevin     | Nolan

Or

1       |    1   |  100 | Kevin Nolan
2       |    2   |  110 | FirstName Surname
3       |    1   |  120 | Kevin Nolan

Thanks

Anders Tornblad · Accepted Answer · 2011-12-16 13:55:44Z

3

That Users table is really badly designed, IMO. If you keep the table layout, please rename it UserProperties. Also, your example code doesn't match your table layout or the suggested results.

That being said, you can do something like this:

var firstnames = from user in context.Users
                 where user.PropertyType == 1
                 select new { Id = user.UserID,
                              Firstname = user.PropertyValue };

var lastnames = from user in context.Users
                where user.PropertyType == 2
                select new { Id = user.UserID,
                             Lastname = user.PropertyValue };

var users = from fn in firstnames
            join ln in lastnames on fn.Id equals ln.Id
            select new { Id = fn.Id,
                         Firstname = fn.Firstname,
                         Lastname = ln.Lastname };

var query = from order in context.Orders
            join user in users on order.UserID equals user.Id
            select new { /*what you need to select*/ };

One good thing about Entity Framework is that the actual SQL queries are deferred as long as possible. So just querying for firstnames and lastnames will not generate a database connection. That comes later. Unfortunately, your table design makes it really difficult to utilize those benefits of EF.

edited Dec 16, 2011 at 13:55

answered Dec 16, 2011 at 13:50

Anders Tornblad

19.4k9 gold badges53 silver badges67 bronze badges

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

2 Comments

Jeff Ogata Over a year ago

+1, imo would be clearer to combine all of them into a single query.

KevDevMan Over a year ago

thanks for the input guys. The design is really bad but I have no control over it. Sorry about the code also, i was trying to simplify the problem and get rid of clutter. Thanks for the solution, I was hoping there. was some EF magic function I had missed to make this easier

Jeff Ogata · Accepted Answer · 2011-12-16 16:05:23Z

Agree with atornblad's answer, but just wanted to point out that you don't need to rely on deferred execution to get the first and last names. You can join to the same table twice, similar to how you would alias the table to join to it multiple times in a sql query:

var query = from o in Orders
            join fn in Users on o.UserId equals fn.UserId
            join ln in Users on fn.UserId equals ln.UserId
            where fn.PropertyType == 1 && ln.PropertyType == 2
            select new 
            { 
                OrderId = o.OrderId, 
                UserId = fn.UserId, 
                Total = o.OrderTotal, 
                FirstName = fn.PropertyValue, 
                LastName = ln.PropertyValue 
            };

Collectives™ on Stack Overflow

Entity Framework - Combining data on two rows

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related