CODEWITHSUNDEEP
c#linqnhibernate
Robyn MacCallum
Robyn MacCallum

Reputation: 206

LINQ Query not pulling all records needed

I am having some trouble with a LINQ query.

This query creates a list of new objects based on entries from a repository. Here is the original query:

var accounts = (from a in entityRepository.Queryable<Account>()
                from l in a.ExternalLogins 
                select new
                {
                    a.ID,
                    FullName = a.FirstName + " " + a.LastName,
                    Status = a.Status == AccountStatus.Closed ? Enums.Status.Inactive : Enums.Status.Active,
                    Login = new
                    {   
                        ConnectionID = l.Connection.ID,
                        l.Connection.ConnectionType,
                        l.Identity    
                    },
                    a.AdminAccess,
                    a.Username,
                    a.Email
                }).ToList();

My issue is that not all a have an a.ExternalLogins. The query is not pulling those accounts because of the additional from statement from l in a.ExternalLogins. I tried modifying the query to:

 var accounts = (from a in entityRepository.Queryable<Account>() 
                 select new
                {
                    a.ID,
                    FullName = a.FirstName + " " + a.LastName,
                    Status = a.Status == AccountStatus.Closed ? Enums.Status.Inactive : Enums.Status.Active,
                    Login = (from l in a.ExternalLogins
                    select new
                    {   
                      ConnectionID = l.Connection.ID,
                      l.Connection.ConnectionType,
                      l.Identity    
                    }),
                    a.AdminAccess,
                    a.Username,
                    a.Email
                }).ToList();

But I am getting a 'System.Reflection.AmbiguousMatchException' exception. From looking up that exception, I am guessing that the reason is because both Account and Connection have the field ID.

Am I headed in the right direction with this? Do I chase down this exception, or is my query not correct?

I apologize if this is trivial; I'm new to LINQ queries, and my google skills have failed me at this point!

Upvotes: 2

Views: 284

Answers (1)

stuartd
stuartd

Reputation: 73243

To do a left outer join in Linq, add a DefaultIfEmpty() call and check for null in the results:

var accounts = (from a in entityRepository.Queryable<Account>()
            from l in a.ExternalLogins.DefaultIfEmpty() 
            select new
            {
                a.ID,
                FullName = a.FirstName + " " + a.LastName,
                Status = a.Status == AccountStatus.Closed ? Enums.Status.Inactive : Enums.Status.Active,
                Login = (l == null) ? null : new
                {   
                    ConnectionID = l.Connection.ID,
                    l.Connection.ConnectionType,
                    l.Identity    
                },
                a.AdminAccess,
                a.Username,
                a.Email
            }).ToList();

Upvotes: 2

Related Questions