Last post Mar 15, 2018 04:41 AM by DA924
Mar 13, 2018 08:21 PM|brainumbc|LINK
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:
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.
Mar 13, 2018 08:50 PM|PatriceSc|LINK
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).
Mar 13, 2018 10:41 PM|DA924|LINK
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.
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>. :)
Mar 14, 2018 12:40 PM|brainumbc|LINK
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.
Mar 14, 2018 02:15 PM|mgebhard|LINK
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.
Mar 14, 2018 03:10 PM|brainumbc|LINK
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.
Mar 14, 2018 05:08 PM|PatriceSc|LINK
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...
Mar 15, 2018 04:41 AM|DA924|LINK
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.
It also seems that you don't understand how to use the MVC UI design pattern effectively when it comes to Separation of Concerns.
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.
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().
var Users = GetUsers().ToList();