Left outer join multiple tables on multiple fields in Linq

Question

User53449276 posted

Hi All,

I got error message when I am trying to left outer join two tables on two fields. The error message is "The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'". There is a red underline under join.

Below is my Linq query:

 var searchResults = (from a in db.courselist
                                   join b in db.Summary on
                                  new { a.subject,a.catalog } equals
                                   new { b.Subject, b.Catalogno } into ab

                                  where  a.degree_id == 1 
                                  orderby a.degree_sequenceNo
                                  from b  in ab.DefaultIfEmpty()
                                  select new
                                  {
                                      Courselist = a,
                                      Summary = b
                                  }
                                ).ToList();


Summary table:
 public partial class Summary
    {
        public Guid id { get; set; }

        [StringLength(50)]
        public string Subject { get; set; }

        [StringLength(50)]
        public string Catalogno { get; set; }

        [StringLength(150)]
        public string CourseTitle { get; set; }

        [StringLength(255)]
        public string URL { get; set; }
    }
Courselist table:
public partial class Courselist
{
...
  [StringLength(50)]
        public string subject { get; set; }

        [StringLength(50)]
        public string catalog { get; set; }

        [StringLength(150)]
        public string coursetitle { get; set; }


        public double? credits { get; set; }
...}

I have referenced Using System.Linq; Any idea where the problem is? Thanks.

Someone gave me the solution. The problem is

new { suject = a.subject, catalog = a.catalog } equals
new { suject = b.subject, catalog = b.Catalogno } into ab

Answer 1

User1120430333 posted

join b in db.Summary on
new { a.subject,a.catalog } equals
new { b.Subject, b.Catalogno } into ab

Where did you come up with this?

join b in db.Summary on a.subject equals b.subject AND a.catalog == b.Catalogno

At best, it should be something similar above I would think. I could be wrong.

Answer 2

User-832373396 posted

Hi jj819,
Sir, after <g class="gr_ gr_78 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="78" data-gr-id="78">tested</g> on local, please refer to this way, it is without error;

1 The first step, to add a new class model

public class subjectcatalog
{
public string catalog { get; set; }
public string Subject { get; set; }
}

2 The second step, just to modify a bit with your code

//after adding a new model
public ActionResult Index()
{
var searchResults = (from a in db.courselist
join b in db.Summary on
new subjectcatalog { Subject= a.subject , catalog= a.catalog} equals
new subjectcatalog { Subject= b.Subject, catalog= b.Catalogno } 

into ab

where a.degree_id == 1
orderby a.degree_sequenceNo
from b in ab.DefaultIfEmpty()
select new
{
Courselist = a,
Summary = b
}
).ToList();

3 Everything is OK!

With regards, Angelina Jolie