0

I keep getting the following error when I execute the query below. I'm not sure how else to put in my condition. I haven't been able to find the same issue with other questions that have posed the same error output.

Unable to create a constant value of type 'Project.Models.Bill'. Only primitive types or enumeration types are supported in this context.

The code:

var billResults = db.Database.SqlQuery<Bill>("exec [dbo].[sp_getBills]").AsQueryable();

var results = db.Bills.Select(
                        a => new
                        {
                            a.Id,
                            a.Col1,
                            a.Col2,
                            ErrorCount = (int) (billResults.Where(x => x.BillResultsId == a.Id).Count())
                        }).Where(a => a.Col1 == "Test123");

I'd appreciate any help - been stuck for the past few hours on this.

Thanks!

RizJa
  • 1,961
  • 1
  • 21
  • 38

1 Answers1

1

The error says that you cannot use a.Id in that expression, only constant values. a.Id depends on the Where clause of db.Bills query.

You can solve this in three ways:

1) Define a class (let's call it X) with properties Id, Col1, Col2 and ErrorCount. Remove ErrorCount from this query and instead of creating an anonymous object, create an object of type X (ErrorCount will not be set). Iterate over the collection and set ErrorCount.

2) Use CROSS APPLY using Entity Framework: read Entity Framework and CROSS/OUTER APPLY

It would be something like this:

from t1 in billResults 
from t2 in db.Bills.Where(t2 => t2.Id== t1.BillResultsId )
select new { ... }

3) Consider creating a Stored Procedure and use a CROSS APPLY join to achieve what you need (read about CROSS APPLY here https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/)

Community
  • 1
  • 1
Francisco Goldenstein
  • 13,299
  • 7
  • 58
  • 74
  • 1
    Went with the stored procedure approach. Thanks for the link about cross apply... learnt something new! +1 – RizJa Jul 24 '15 at 06:38