• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 51
  • Last Modified:

Linq/Lambda left join

Hello... new to linq lambda - what is wrong with my query? - I get 'sequence contains no elements' error when there are no providerSpecialties records for the given provider.

This should behave like a left join from the provider entity.

            
             return DBContext.Provider.GroupJoin(DBContext.ProviderSpecialties,
                    p => p.providerId, ps => ps.providerSpecialtyId, (p, ps) => new { Provider = p, ProviderSpecialties = ps.DefaultIfEmpty()})
                    .SelectMany(a => a.ProviderSpecialties
                    .Select(b => new { Provider = a.Provider, ProviderSpecialties = b }))
                    .Where(w => w.ProviderSpecialties.isPrimary == true)
                    .Where (w => w.Provider.providerId == providerId)
                    .Select(m => new ProviderDetail
                    {
                        providerId = m.Provider.providerId,
                        NPI = m.Provider.NPI,
                        licenseNumber = m.Provider.licenseNumber,
                        licenseExpiration = m.Provider.licenseExpiration,
                        licenseStateId = m.Provider.licenseState,
                        licenseStateName = m.Provider.State.abbreviation,
                        networkLevelId = m.Provider.networkLevel,
                        networkLevelName = m.Provider.NetworkLevel1.name,
                        firstName = m.Provider.firstName,
                        lastName = m.Provider.lastName,
                        title = m.Provider.title,
                        birthDate = m.Provider.birthDate,
                        sex = m.Provider.sex,
                        primarySpecialtyId = m.ProviderSpecialties.Specialty.specialtyId,
                        referrable = m.Provider.referrable,
                        photoUrl = m.Provider.photoUrl,
                        emailAddress = m.Provider.emailAddress,
                        websiteUrl = m.Provider.websiteUrl,
                        recordStatus = m.Provider.recordStatus
                    }).Single();

Open in new window


In SQL I would write:

select p.*, ps.specialtyId, ps.specialtyName
from provider p
left join providerspecialties ps
on ps.providerId = p.providerId
and ps.isPrimary = 1
left join Specialty s
on ps.specialtyId = s.specialtyId

Open in new window


Thanks
0
sailing_12
Asked:
sailing_12
  • 2
1 Solution
 
sailing_12Author Commented:
Was able to solve this-

return DBContext.Provider
		.GroupJoin(PMTDBContext.ProviderSpecialties, p => p.providerId, ps => ps.providerId, (x, y) => new { Provider = x, ProviderSpecialties = y })
		.Where(w => w.Provider.providerId == providerId)
		.SelectMany(x => x.ProviderSpecialties.Where(w => w.isPrimary == true)
		.DefaultIfEmpty(), (x, y) => new { Provider = x.Provider, ProviderSpecialties = y })
		.Select(m => new ProviderDetail
		{
			providerId = m.Provider.providerId,
			NPI = m.Provider.NPI,
			licenseNumber = m.Provider.licenseNumber,
			licenseExpiration = m.Provider.licenseExpiration,
			licenseStateId = m.Provider.licenseState,
			licenseStateName = m.Provider.State.abbreviation,
			networkLevelId = m.Provider.networkLevel,
			networkLevelName = m.Provider.NetworkLevel1.name,
			firstName = m.Provider.firstName,
			lastName = m.Provider.lastName,
			title = m.Provider.title,
			birthDate = m.Provider.birthDate,
			sex = m.Provider.sex,
			primarySpecialtyName = m.ProviderSpecialties.Specialty.epicName,
			primarySpecialtyId = m.ProviderSpecialties.specialtyId == null ? 0 : m.ProviderSpecialties.specialtyId,
			referrable = m.Provider.referrable,
			photoUrl = m.Provider.photoUrl,
			emailAddress = m.Provider.emailAddress,
			websiteUrl = m.Provider.websiteUrl,
			recordStatus = m.Provider.recordStatus
		}).Single();

Open in new window

0
 
sailing_12Author Commented:
Needed to add the where isprimary = true to the .selectmany instead of on the main query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now