Expression Tree error: Unable to create a constant value of type

Question

Here is the error : Unable to create a constant value of type 'mvcinfosite.ViewModels.GrpSearchHolder'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

How can I resolve that error. I Do a litle example to show you my problem. In my real project, MyGrp1,MyGrp2,MyGrp3 is replace by ListBox. I use it to filter my data.

        public class MyGroupHolder
        {
            public string GrpName { get; set; }
            public List<int ?> ListSelectedGrpDescID { get; set; }
        }


        public ActionResult Index()
        {
            //Database Context
            DBEntities db = EntityFactory.GetEntity();

            //Variables
            List<MyGroupHolder> ListGrpHolder = new List<MyGroupHolder>();

            //Imagine a 3 listbox (MyGrp1,MyGrp2,MyGrp3) 
            //Each listbox contains selected value.
            MyGroupHolder MyGrp1 = new MyGroupHolder();
            MyGrp1.GrpName = "Grp 1 Test";
            MyGrp1.ListSelectedGrpDescID = new List<int?>();
            MyGrp1.ListSelectedGrpDescID.Add(55);


            MyGroupHolder MyGrp2 = new MyGroupHolder();
            MyGrp2.GrpName = "Grp 2 Test";
            MyGrp2.ListSelectedGrpDescID = new List<int?>();
            MyGrp2.ListSelectedGrpDescID.Add(56);


            MyGroupHolder MyGrp3 = new MyGroupHolder();
            MyGrp3.GrpName = "Grp 3 Test";
            MyGrp3.ListSelectedGrpDescID = new List<int?>();
            MyGrp3.ListSelectedGrpDescID.Add(57);

            ListGrpHolder.Add(MyGrp1);
            ListGrpHolder.Add(MyGrp2);
            ListGrpHolder.Add(MyGrp3);

            //Getting a list of Locations base on the Group Filter
            var ListLocation = db.Locations.Where(p => ListGrpHolder.Any(pg => pg.ListSelectedGrpDescID.Count == 0 || p.GroupLocations.Select(sg => sg.GrpDescID).Intersect(pg.ListSelectedGrpDescID).Any())).ToList();


            return View();
        }

score 1 · Answer 1 · answered Oct 28 '11 at 12:50

You cannot pass objects from your application to linq-to-entities queries. You must extract values and pass them as conditions. Just start of your query shows the problem:

.Where(p => ListGrpHolder.Any(...

How should SQL server responsible for executing Linq-to-entity query know what ListGrpHolder is (it lives in memory of your application) and what value it contains?

I don't exactly understand your query and what it should do but simply you must strictly differ between linq-to-entities and linq-to-objects. The first one is executed on SQL server and it allows passing only simple types to query. The second one is executed in your application and you can use any object and linq construction for them but if you want to use it with data from SQL server you must first load all of them to your application and make filtering in the memory of your application server.

Is there a way with a custom expression tree to convert my query into a valid expression? — Jean-Francois, Nov 03 '11 at 15:43

score 0 · Answer 2 · answered Nov 08 '11 at 01:41

0

convert your query to use

from l in db.Locations where mycalculatedlocalids.Contains(l.id) select l;

IQueryable contains provider info and IEnumerable (ListGrpHolder) does not. This is why you cannot run your query on sql server.

answered Nov 08 '11 at 01:41

Leblanc Meneses

3,001
1
23
26

score 0 · Answer 3 · answered Nov 10 '11 at 12:27

I converted query to use LINQ instead of the Lambda methods. It is easier for me to read queries when they are written in LINQ syntax.

How does the db.Location relate to pg.ListSelectedGrpDescID.Count == 0? What should db.Location return if it is 0? I left this part of the query out for now.

It can be a good idea to break the problem up into steps when trying to find a solution. This is a first step towards the solution.

First, we can refactor the ListGrpHolder ID's into another statement. This will return only the int value and make the ListLocation query easier to read.

var SelectedIds = ListGrpHolder.Select(pg => pg.ListSelectedGrpDescID).SelectMany(i => i);
var ListLocation = (from loc in db.Locations
                    from grp in loc.GroupLocations
                    where SelectedIds.Contains(grp.GrpDescID)
                    select loc).ToList();

If we are not able to simplify or change the query to get the results you need, then we can look into using an Expression Tree.

Expression Tree error: Unable to create a constant value of type

3 Answers3