CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-sql
Bozoo Hambozo Alozo
Bozoo Hambozo Alozo

Reputation: 23

Error in linq-to-sql: Unable to create a constant value of type

I don't know what's wrong with this query - it keeps giving me this error:

Unable to create a constant value of type 'OvertimeProject.DataCore.tbl_Promotion'. Only primitive types or enumeration types are supported in this context.

Here is my linq statement:

public IEnumerable<StaffJoin> GetOrderStaffByDepartment(int DepID, DateTime DATE)
{
    using (var Context = new CRMDBEntities())
    {
        var result = (from pro2 in Context.tbl_Promotion
                      where pro2.PromotionDate <= DATE && pro2.DepID==DepID
                      select pro2).ToList();

        var result2 = (from Re in result
                      group Re by Re.StaffID into g2
                      join prop in Context.tbl_Promotion on g2.Max(c => c.PromotionID) equals prop.PromotionID
                      select prop).ToList();

        var result3 = (from s in Context.tbl_STaff
                       join
                       promotion in result2 on s.StaffID equals promotion.StaffID
                       join
                       position in Context.tbl_Position on promotion.PositionID equals position.PositionID
                       select new StaffJoin{Staff= s,Promotion= promotion,Position= position}).ToList();

        return result3;
    }
}

Here is my class which I want to fill with returned data:

 public class StaffJoin
 {
     public tbl_STaff Staff { get; set; }
     public tbl_Promotion Promotion { get; set; }
     public tbl_Position Position { get; set; }  
 }

... And here is my model instructure with navigated properties:

tbl_Staff                  tbl_Promotion                 tbl_Position
---------                 --------------                 -------------
StaffID 0.1                PromotionID               0.1 PositionID
DepID                      PromotionDate                 PositionName
StaffName                 * StaffID                      PositionBase
Chek                       PositionID *                  Avaiilibilty
                           Avilibility
-------                   ---------------               --------------
Navigation                Navigation                    Navigation 
----------                --------------                ----------------
tbl_Department             tbl_Position                  tbl_Promotion
tbl_Promotion              tbl_Staff

What I need is this:

All staff for a specific department plus the max promotion for each staff member after a specific date, plus the position info related to the promotion

Upvotes: 1

Views: 1128

Answers (1)

Aron
Aron

Reputation: 15772

Your problem is that your code basically reads like this...

public IEnumerable<StaffJoin> GetOrderStaffByDepartment(int DepID, DateTime DATE)
{
    using (var Context = new CRMDBEntities())
    {
        List<Promotion> result = (from pro2 in Context.tbl_Promotion
                                where pro2.PromotionDate <= DATE && pro2.DepID==DepID
                                select pro2).ToList();

        List<Promotion> result2 = (from Re in result
                                group Re by Re.StaffID into g2
                                join prop in Context.tbl_Promotion on g2.Max(c => c.PromotionID) equals prop.PromotionID
                                select prop).ToList();

        var result3 = (from s in Context.tbl_STaff
                    join
                    promotion in result2 on s.StaffID equals promotion.StaffID
                    join
                    position in Context.tbl_Position on promotion.PositionID equals position.PositionID
                    select new StaffJoin{Staff= s,Promotion= promotion,Position= position}).ToList();

        return result3;
    }
}

Now on result3, you are trying to put two List<Propmotion> into a Linq query, which LinqToSQL needs to convert into SQL. Obviously SQL can't handle .net user classes, so it fails.

If you consider Jeff's change you will get 

public IEnumerable<StaffJoin> GetOrderStaffByDepartment(int DepID, DateTime DATE)
{
    using (var Context = new CRMDBEntities())
    {
        IQueryable<Promotion> result = (from pro2 in Context.tbl_Promotion
                                where pro2.PromotionDate <= DATE && pro2.DepID==DepID
                                select pro2);

        IQueryable<Promotion> result2 = (from Re in result
                                group Re by Re.StaffID into g2
                                join prop in Context.tbl_Promotion on g2.Max(c => c.PromotionID) equals prop.PromotionID
                                select prop);

        var result3 = (from s in Context.tbl_STaff
                    join
                    promotion in result2 on s.StaffID equals promotion.StaffID
                    join
                    position in Context.tbl_Position on promotion.PositionID equals position.PositionID
                    select new StaffJoin{Staff= s,Promotion= promotion,Position= position}).ToList();

        return result3;
    }
}

Now, LinqToSQL knows how to deal with IQueryables. It could just inline the SQL in result and result2 as subqueries. Hence the difference.

However the last thing you need to change is the last line. IQueryable<T> represents a query, work that hasn't happened yet. It is when you call .GetEnumerator() that the query actually runs. However, that will only occur in the consuming code, by which point the CRMDBEntities will have been disposed. Soooo.....

public IEnumerable<StaffJoin> GetOrderStaffByDepartment(int DepID, DateTime DATE)
{
    using (var Context = new CRMDBEntities())
    {
        IQueryable<Promotion> result = (from pro2 in Context.tbl_Promotion
                                where pro2.PromotionDate <= DATE && pro2.DepID==DepID
                                select pro2);

        IQueryable<Promotion> result2 = (from Re in result
                                group Re by Re.StaffID into g2
                                join prop in Context.tbl_Promotion on g2.Max(c => c.PromotionID) equals prop.PromotionID
                                select prop);

        var result3 = (from s in Context.tbl_STaff
                    join
                    promotion in result2 on s.StaffID equals promotion.StaffID
                    join
                    position in Context.tbl_Position on promotion.PositionID equals position.PositionID
                    select new StaffJoin{Staff= s,Promotion= promotion,Position= position}).ToList();

        return result3.ToList();
    }
}

Or with the vars back...

public IEnumerable<StaffJoin> GetOrderStaffByDepartment(int DepID, DateTime DATE)
{
    using (var Context = new CRMDBEntities())
    {
        var result = (from pro2 in Context.tbl_Promotion
                                where pro2.PromotionDate <= DATE && pro2.DepID==DepID
                                select pro2);

        var result2 = (from Re in result
                                group Re by Re.StaffID into g2
                                join prop in Context.tbl_Promotion on g2.Max(c => c.PromotionID) equals prop.PromotionID
                                select prop);

        var result3 = (from s in Context.tbl_STaff
                    join
                    promotion in result2 on s.StaffID equals promotion.StaffID
                    join
                    position in Context.tbl_Position on promotion.PositionID equals position.PositionID
                    select new StaffJoin{Staff= s,Promotion= promotion,Position= position}).ToList();

        return result3.ToList();
    }
}

Upvotes: 2

Related Questions