Left join on linq query giving error

Question

I have simple sql Left join query as:

select a.firstName+' '+a.lastName Name from account a left join
EH_PP_TeacherEvaluations b
on a.id=b.EH_PP_TE_TeacherAcctID

I wanted to do it in linq.

I tried to do it as follows:

 List<Entity.TeacherEval> list = new List<Entity.TeacherEval>();
                list = (from a in context.accounts  join b in context.EH_PP_TeacherEvaluations on
                        a equals b.EH_PP_TE_TeacherAcctID into te
                        from b in te.DefaultIfEmpty()
                        select new { a.firstName+' '+a.lastName}
                        ).ToList();
                return list;

But its not working.

Error on 'join' :

Type interface failed in call to Group join.

and on row:

 select new { a.firstName+' '+a.lastName}

Please help me.

Where i am making mistake, i am new with linq.

Edit 1 :

  public class TeacherEval
    {
        public Guid ID { get; set; }
        public DateTime? ProcessStartDate { get; set; }
        public DateTime? ProcessEndDate { get; set; }
        public Guid? AccountID { get; set; }
        public Guid? StatusId { get; set; }

        public TeacherEval() { }

        public TeacherEval(DB.EH_PP_TeacherEvaluation item)
        {
            this.ID = item.EH_PP_TE_TeacherEvalID;
            this.ProcessStartDate = item.EH_PP_TE_TeacherEvalProcessStartDate;
            this.ProcessEndDate = item.EH_PP_TE_TeacherEvalProcessEndDate;
            this.AccountID = item.EH_PP_TE_TeacherAcctID;
            this.StatusId = item.EH_PP_TESRT_TeacherEvalStatusIDEH;
        }

        public DB.EH_PP_TeacherEvaluation ToDB()
        {
            var rec = new DB.EH_PP_TeacherEvaluation();
            rec.EH_PP_TE_TeacherEvalProcessStartDate = this.ProcessStartDate;
            rec.EH_PP_TE_TeacherEvalProcessEndDate = this.ProcessEndDate;
            rec.EH_PP_TE_TeacherAcctID = this.AccountID;
            rec.EH_PP_TESRT_TeacherEvalStatusIDEH = this.StatusId;
            return rec;
        }

        public DB.EH_PP_TeacherEvaluation ToDB(DB.EH_PP_TeacherEvaluation rec)
        {
            rec.EH_PP_TE_TeacherEvalProcessStartDate = this.ProcessStartDate;
            rec.EH_PP_TE_TeacherEvalProcessEndDate = this.ProcessEndDate;
            rec.EH_PP_TE_TeacherAcctID = this.AccountID;
            rec.EH_PP_TESRT_TeacherEvalStatusIDEH = this.StatusId;
            return rec;
        }
    }

Answer 1

Try this:

List<Entity.TeacherEval> list =  (from a in context.accounts join b in 
                    context.EH_PP_TeacherEvaluations on
                    a.id equals b.EH_PP_TE_TeacherAcctID into te
                    from b in te.DefaultIfEmpty()
                    select new Entity.TeacherEval { Name = a.firstName+' '+a.lastName}
                    ).ToList();

You need to add a Property called Name in the class Entity.TeacherEval

OR

you can simply call the list into a variable like this

var list = (from a in context.accounts join b in 
                    context.EH_PP_TeacherEvaluations on
                    a.id equals b.EH_PP_TE_TeacherAcctID into te
                    from b in te.DefaultIfEmpty()
                    select new { Name = a.firstName+' '+a.lastName}
                    ).ToList();

Answer 2

do like this:

List<Entity.TeacherEval> list = (from a in context.accounts  
                                 join b in context.EH_PP_TeacherEvaluations 
                                 on a.id equals b.EH_PP_TE_TeacherAcctID into te
                                 from b in te.DefaultIfEmpty()
                                 select new Entity.TeacherEval 
                                 {Name = a.firstName+" "+a.lastName}).
                                 ToList<Entity.TeacherEval>();

Your class should have property of Name:

public class TeacherEval
{
public string Name {get;set;}
} 

Answer 3

Try this:

var result= (
        from a in context.accounts
        from b in context.EH_PP_TeacherEvaluations
            .Where(w=>w.EH_PP_TE_TeacherAcctID == a.id).DefaultIfEmpty()
        select new {name= a.firstName+" "+a.lastName}
    ).ToList();

This will be translate to a LEFT JOIN

Edit

It looks to me like you want to fill a new object. Do I would suggest you doing something like this:

public class ResultDTO
{
    public string Name { get; set; }
}

And then the query like this:

var result= (from a in context.accounts  
    join b in context.EH_PP_TeacherEvaluations on 
        a.id equals b.EH_PP_TE_TeacherAcctID 
        into te
        from b in te.DefaultIfEmpty()
    select new ResultDTO
    {
        Name= a.firstName+" "+a.lastName
    }
    ).ToList();

This will result into a List<ResultDTO>

Edit 2

Alternative you can also do this. If you just want the string:

var result= (from a in context.accounts  
        join b in context.EH_PP_TeacherEvaluations on 
            a.id equals b.EH_PP_TE_TeacherAcctID 
            into te
            from b in te.DefaultIfEmpty()
        select a.firstName+" "+a.lastName
        ).ToList();

This will result into a List<string>