1

Below is my ERD and sample data. Note, I'm using Entity Framework and Code first to control my database.

enter image description here

For the project named Vacation, return all the DISTINCT users who have a "true" value in UserBooleanAttributes table for either the Parents or Teens rows defined in the UserAttributes table.

Here is my current attempt:

                var myQuery =
                    from P in context.Projects
                    join UA in context.UserAttributes on P.ProjectID equals UA.ProjectID
                    join UBA in context.UserBooleanAttributes on UA.UserAttributeID equals UBA.UserAttributeID
                    join U in context.Users on UBA.UserID equals U.UserID
                    where P.ProjectID == 1
                    where UBA.Value == true
                    where (UA.UserAttributeID == 1 || UA.UserAttributeID == 2)
                    select new { uba = U };

This returns 6 users, with e@acme.org being listed twice. Is there a LINQ way of returning distinct values? I suppose I could convert this to a list then filter, but I'd rather have the Database do the work.

I'd rather avoid using lambda expressions if possible. Once again, I want the database to do the work, and not have to write code to union/intersect result groups.

WhiskerBiscuit
  • 4,795
  • 8
  • 62
  • 100
  • 3
    Why do you want to avoid lambda expressions? Also, this is 2 questions. Please stick to one question per... question! – DavidG Jul 17 '15 at 01:23
  • Oh, and to answer your first problem.... http://stackoverflow.com/questions/4318909/linq-to-sql-distinct-and-orderby – DavidG Jul 17 '15 at 01:26
  • I want to avoid lambda expressions because I find the language less natural for devs used to SQL. – WhiskerBiscuit Jul 17 '15 at 01:28
  • 2
    Lambda is part of C#, the devs should get used to it. – DavidG Jul 17 '15 at 01:29
  • Changing it to var myQuery = (from P ... select new { uba = U }).Distinct(); did the trick. Just for kicks, can you add a solution of doing the same query in lambda format? (Yes, they should get used to it) – WhiskerBiscuit Jul 17 '15 at 01:34

0 Answers0