Entity Framework 6 mapping a custom SQL query to an Entity
asp.net-mvc c# entity-framework entity-framework-6
Question
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,
SampleLocation.LocationId
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?
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
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
{
s.Id,
s.CheckedInDate,
l.Name,
l.Size,
l.LocationId
};
Dapper:
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,
SampleLocation.LocationId
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.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow