Entity Framework 6 mapping a custom SQL query to an Entity

asp.net-mvc c# entity-framework entity-framework-6


I am very new to ASP.NET MVC and Entity Framework at the moment. I've been working with it for about 3 months at the current moment in time.

I have an ASP.NET MVC 5 application that's running Entity Framework 6. This is a code first approach from an existing database with auto migrations enabled so all of my Entity classes are auto generated. I am trying to add a view to my MVC application that returns a specific result set. Currently the previous developer has the application set up to only accept an Entity class to display data to a DataTable DataTables.net. To clarify further:

I have two Entity tables in my model that are tables in MySQL.

| Samples           | SampleLocation |
| Id                |     LocationId |        
| DateAssigned      |           Name |      
| CheckedInDate     |           Size |         
| SampleLocationId  |                |        
| ...etc            |         ...etc |          

What I'm trying to accomplish is querying both tables and returning the results to my MVC application in a view. From there run an Update and update a couple of columns in the Samples table. Below is the roughly the query that returns the results I need.

SELECT Samples.Id, samples.CheckedInDate, SampleLocation.Name, SampleLocation.Size, 
FROM (Samples join SampleLocation 
ON ((Samples.SampleLocationId = SampleLocation.LocationId))) 
WHERE  isnull(samples.CheckedInDate) ORDER BY samples.Id

From the research that I have done there are a few ways to accomplish this. The ways that I've tried that would give me a class I could use are creating a stored procedure and then updating the model - this breaks the model and unmaps every single entity in the model. I have tried creating a view with the query to add to the model - but this breaks it as well and unmaps everything. I later found out that this is a bug.

So my question is, how can I map a query to an Entity that return results to a view? Is there a better way to go about this?

2/5/2018 8:47:00 PM

Accepted Answer

This is the solution to my problem. Basically manually creating an Entity within the XML and mapping it to a virtual table.Entity Framework DefiningQuery

2/15/2018 5:14:00 PM

Popular Answer

There are several ways to accomplish what you want to do, using either Entity Framework only or extra tools like Dapper

Entity Framework Only:

First you can use Linq to extract the data, I'll look something like this:

var list = from s in Samples
           join l in SampleLocations
               on s.Id equals l.LocationId
           where s.CheckedInDate == null
           select new


The second method is using dapper, the only real difference here is that you would be working with your queries directly, so instead of linq you had something like this:

Connection.Query(@"SELECT Samples.Id, samples.CheckedInDate,  
                     SampleLocation.Name, SampleLocation.Size, 
                   FROM Samples
                     join SampleLocation 
                       ON Samples.SampleLocationId = SampleLocation.LocationId
                   WHERE isnull(samples.CheckedInDate) ORDER BY samples.Id");

For both:

In your update method, you first have to retrieve the entities, for that you can use the DbSet.Find method or another query, after that you call DbConext.SaveChanges.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow