0

I'm trying to convert the next query with left joins in order to get a IList of one of my model objects.

This is the query:

select M.MilestoneID, MT.MilestoneTypeID, M.RCCAID
from Sourcing.MilestoneType MT
left join Sourcing.Milestone M on M.MilestoneTypeID = MT.MilestoneTypeID
where M.MilestoneID is null or (M.RCCAID = 1)

Output I get in SQL

MilestoneID | TypeID | RCCAID
------------+--------+--------
      1     |    1   |   1
    NULL    |    1   |  NULL

I have tried with this

var list  = from mt in db.MilestoneType
                        join m in db.Milestone on mt.MilestoneTypeID equals m.MilestoneTypeID
                        where m.RCCAID == id || m.MilestoneID == 0
                        select new Milestone
                        {
                            MilestoneID = m.MilestoneID,
                            RCCAID = m.RCCAID,
                            MilestoneTypeID = mt.MilestoneTypeID
                        };

            var list1 = db.MilestoneType.Join(db.Milestone,
                mt => mt.MilestoneTypeID,
                m => m.MilestoneTypeID,
                (mt, m) =>  new Milestone()
                {
                    MilestoneID = m.MilestoneID,
                    RCCAID = m.RCCAID,
                    MilestoneTypeID = mt.MilestoneTypeID
                }).Where(a => a.RCCAID == id ||a.MilestoneID==0).ToList();

The first one does not work.

And I get this error in the second one:

The entity or complex type 'Model.Milestone' cannot be constructed in a LINQ to Entities query

I'm trying to get all the milestone per RCCA with all the milestone types even if a milestone type is not created yet.

I expect to get an IList<Milestone>.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alejandro Gomez Guerrero
  • 348
  • 2
  • 3
  • 10
  • It is easier to do left join with query syntax - see my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) but in fluent syntax you need to use `GroupJoin` and `DefaultIfEmpty`. – NetMage Jul 11 '19 at 18:04
  • You cannot project to an Entity which is a part of your db context in this case Milestone...you will either need to cast to a custom class or an anonymous type.. – gkb Jul 11 '19 at 18:05
  • *The first one does not work* --please explain. Note that in both cases you're doing an inner join so milestones with non-existing milestone types won't appear. – Gert Arnold Jul 11 '19 at 19:10
  • Possible duplicate of [LEFT OUTER JOIN in LINQ](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) – huysentruitw Jul 29 '19 at 12:39

0 Answers0