locked
Unable to create a constant value of type ''. Only primitive types or enumeration types are supported in this context. RRS feed

  • Question

  • User-2066416675 posted

    In my MVC controller I have two lists created from completely different models. One model contains a master list of users. Another model contains a smaller subset of "assigned users". I'm trying to create a list of users from this master list that isn't in the other list. I've seen a lot of examples out there, but they only seem to work when the two lists are based on the same model.. I tried this::

    var unassignedusers = db.vwUsers.Where(item1 => !Users.Any(item2 => item2.UserID == item1.UserID)).ToList();

    db.vwUsers is a model based on a view that contains all users (my master list). 

    Users is a complex type returned from a stored procedure that lists all assigned users and I create it like this:

    var Users = db.spAssignedUsers_List(param1, param2).ToList();

    I get this error:

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

    I don't know what it's it's talking about with a "constant value" I'm just trying to compare the "UserID" value from one list type to the "UserID" from another list type.

    Tuesday, March 13, 2018 8:21 PM

Answers

  • User753101303 posted

    Hi,

    You could try (as you have already an spAssignedUsers SP another option could be to have an spUnassignedUsers as well):

    var Users = db.spAssignedUsers_List(param1, param2).ToList().Select(o=>o.UserID); // create a list with just user ids
    var unassignedusers = db.vwUsers.Where(item1 => !Users.Contains(item1.UserID)).ToList(); // generate a NOT IN (<list of user ids>))
    

    The source issue is that Linq doesn't know how to translate your list of complex client side types to a SQL construct. If you were not using a SP another option would have been to drop ToList (to combine and run both SQL queries on the server side).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 13, 2018 8:50 PM

All replies

  • User753101303 posted

    Hi,

    You could try (as you have already an spAssignedUsers SP another option could be to have an spUnassignedUsers as well):

    var Users = db.spAssignedUsers_List(param1, param2).ToList().Select(o=>o.UserID); // create a list with just user ids
    var unassignedusers = db.vwUsers.Where(item1 => !Users.Contains(item1.UserID)).ToList(); // generate a NOT IN (<list of user ids>))
    

    The source issue is that Linq doesn't know how to translate your list of complex client side types to a SQL construct. If you were not using a SP another option would have been to drop ToList (to combine and run both SQL queries on the server side).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 13, 2018 8:50 PM
  • User1120430333 posted

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

    The returned results of the stored procedure cannot made into objects is the bottom line here with objects using primitive type properties I suspect or an enumeration type.

    primitive types....

     https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/built-in-types-table

    enumeration type

    https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/enumeration-types

    I think you need to set a debug breakpoint, use Quickwatch on Users without the Tolist(), which I think is part of your problem  is ToList(), and see what is in Users.

    About ToList(), it  may be applicable if the sproc was returning  a list of C# custom objects out of it that could be place into a List<T>. :)

    https://msdn.microsoft.com/en-us/library/bb342261%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

    https://www.dotnetperls.com/tolist

    Tuesday, March 13, 2018 10:41 PM
  • User-2066416675 posted

    Thanks, guys. i guess just having an extra sproc to get the unassigned users would be the answer.

    I've been working with MVC for a couple of years now and I find that when dealing with tables, or even views, it works great. However MVC seems to have a lot of limitations when your models are based on stored procedures... which... in the real world... seems to be the case quite often. Very simple sites dealing with things like user profiles, orders, etc seem ideal for MVC but when you have a lot of complicated applications, you're going to end up with a lot of complex logic that needs to be kept inside TSQL so stored procedures are just necessary.  Especially when you want your results to be contingent upon parameters.

    Wednesday, March 14, 2018 12:40 PM
  • User475983607 posted

    Stored procedure are invoked via ADO.NET or an ORM like Entity Framework usually in a data access or business layer.  MVC does not invoke stored procedure directly which might be part of the problem.  

    Wednesday, March 14, 2018 2:15 PM
  • User-2066416675 posted

    I've used plenty of stored procedures in MVC before as a basis for models and I've never really had a problem (other than the fact that you cant create foreign keys on the results to link back to a different table in the Model). This is the first time I've really run into an issue with sprocs and LINQ.

    Wednesday, March 14, 2018 3:10 PM
  • User753101303 posted

    The root cause is that it is much harder to reuse the resultset returned by a stored procedure inside a bigger SQL statement. It is much easier for tables, views and maybe table valued functions.

    So if using SPs, it is likely best to always call a single SP that returns the needed resultset rather than to try to use a SP and then have to combine the returned result with something else to produce the final result you want.

    You may have the same issue if using ToList with a DbSet (the problem being that once your resultset is retrieved client side, Linq doesn't know how to create a SQL statement that would reproduce the same resultset from client side data) but the fix is easy : you just drop ToList() so that this resultset is combined server side with some other resultset (as a single server side SQL statement) to create the final result you want...

    Wednesday, March 14, 2018 5:08 PM
  • User1120430333 posted

    I think you confuse the persistence model with the domain model. The resultset is part of the persistence model, and it doesn't seem that you understand how to transition between the domain model and the persistence model effectively.

    http://blog.sapiensworks.com/post/2012/04/07/Just-Stop-It!-The-Domain-Model-Is-Not-The-Persistence-Model.aspx

    <copied>

    • The Domain Model models real-life problems and solutions, it models BEHAVIOR.
    • The Persistence Model models what and how data is stored, it models STORAGE STRUCTURE. See? They have pretty different  purposes. The domain is the reason the application exists and everything gravitates around it. The domain should not depend on anything, especially not on a persistence IMPLEMENTATION DETAIL like EF or NH. When you design the Domain Entities, they don't know anything about persistence. Persistence, database, doesn't exist.

    <end>

    It also seems that you don't understand how to use the MVC UI design pattern effectively when it comes to Separation of Concerns.

    https://en.wikipedia.org/wiki/Separation_of_concerns

    https://www.c-sharpcorner.com/UploadFile/56fb14/understanding-separation-of-concern-and-Asp-Net-mvc/

    https://docs.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/overview/understanding-models-views-and-controllers-cs

    <copied>

    An MVC model contains all of your application logic that is not contained in a view or a controller. The model should contain all of your application business logic, validation logic, and database access logic. For example, if you are using the Microsoft Entity Framework to access your database, then you would create your Entity Framework classes (your .edmx file) in the Models folder. OR the model could have called the DAL or a Repository.

    <end>

    Also this, because the DTO allows loose coupling,  an abstraction away from the underlying DB technology and also allows for the formulation of complex types, which could have been what was returned instead of the resultset of the sproc. A method could have called the sproc from a method that retuned the List<DTO> that would have worked with the ToList().

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    var Users = GetUsers().ToList();

    Thursday, March 15, 2018 4:41 AM