CODEWITHSUNDEEP
c#linqjoin
birdus
birdus

Reputation: 7504

LINQ to DataSet multi-table left join

There are numerous places where it's shown how to LEFT join two tables using LINQ, but I'm having trouble adding more into the join (I have 15 tables). I thought I had the pattern down, but I'm getting an error. By the way, this is against a strongly typed DataSet, although I don't suspect that matters. Although I actually have many more tables to add into the join, I'm trying to get it working initially with fewer joins (the asterisks are where the errors are occuring):

var results = 
    from e in DataSetHelper.ds.Employee
    join es in DataSetHelper.ds.EmployeeSkill on e.EmployeeId equals es.EmployeeId    into esGroup from esItem in esGroup.DefaultIfEmpty()
    join s in DataSetHelper.ds.Skill on **es.SkillId** equals s.SkillId                   into skillGroup from skillItem in skillGroup.DefaultIfEmpty()
    join er in DataSetHelper.ds.EmployeeRole on e.EmployeeId equals er.EmployeeId     into erGroup from erItem in erGroup.DefaultIfEmpty()
    join r in DataSetHelper.ds.Role on **er.RoleId** equals r.RoleId                      into rGroup from rItem in rGroup.DefaultIfEmpty()

I'm getting two errors (the same thing, but on different joins). They are on the 3rd and 5th lines of the query.

For es.SkillId, the error is The name 'es' does not exist in the current context.

For er.RoleId, the error is The name 'er' does not exist in the current context.

Again, I'll need to use this pattern for another ten joins, so I'm hoping the pattern doesn't increase in complexity as I go.

Upvotes: 2

Views: 448

Answers (2)

birdus
birdus

Reputation: 7504

This works. I also had to account for the possibility of a null on the join column. I used the null-conditional operator for that. Unfortunately, using an in-memory Dataset is just as slow as using SQL Server (and I don't even have all the joins or all the where clause tests in place). Seems like I need to use a different approach.

var results = from e in _data.ds.Employee
              join es in _data.ds.EmployeeSkill on e.EmployeeId equals es.EmployeeId        into esGroup from esItem in esGroup.DefaultIfEmpty()
              join s in _data.ds.Skill on esItem?.SkillId equals s.SkillId                  into sGroup from skillItem in sGroup.DefaultIfEmpty()
              join er in _data.ds.EmployeeRole on e.EmployeeId equals er.EmployeeId         into erGroup from erItem in erGroup.DefaultIfEmpty()
              join r in _data.ds.Role on erItem?.RoleId equals r.RoleId                     into rGroup from rItem in rGroup.DefaultIfEmpty()
              join et in _data.ds.EmployeeTechnology on e.EmployeeId equals et.EmployeeId   into etGroup from etItem in etGroup.DefaultIfEmpty()
              join t in _data.ds.Technology on etItem?.TechnologyId equals t.TechnologyId   into tGroup from tItem in etGroup.DefaultIfEmpty()
              where
                e.FirstName.IndexOf(searchTerm, StringComparison.OrdinalIgnoreCase) >= 0 ||
                e.LastName.IndexOf(searchTerm, StringComparison.OrdinalIgnoreCase) >= 0 ||
                e.RMMarket.IndexOf(searchTerm, StringComparison.OrdinalIgnoreCase) >= 0 ||
                !e.IsSummaryNull() && e.Summary.IndexOf(searchTerm, StringComparison.OrdinalIgnoreCase) >= 0
              select new SearchResult
              {
                  EmployeeId = e.EmployeeId,
                  Name = e.FirstName + " " + e.LastName,
                  Title = e.Title,
                  ImageUrl = e.IsImageUrlNull() ? string.Empty : e.ImageUrl,
                  Market = e.RMMarket,
                  Group = e.Group,
                  Summary = e.IsSummaryNull() ? string.Empty : e.Summary.Substring(1, e.Summary.Length < summaryLength ? e.Summary.Length - 1 : summaryLength),
                  AdUserName = e.AdUserName
              };

Upvotes: 1

Anup Sharma
Anup Sharma

Reputation: 2083

Try this

var results = DataSetHelper.ds.Employee
.Join(DataSetHelper.ds.EmployeeSkill, e => e.EmployeeId, es => es.EmployeeId, (e, es) => new { e, es })
.Join(DataSetHelper.ds.Skill, esGroup  => esGroup.es.SkillId, s => s.SkillId, (esGroup, s) => new { esGroup.e, esGroup.es, s })
.Join(....

I have shown till the first many to many relation. It's going to be bit long for everything but I hope you can manage it.

Upvotes: 0

Related Questions