Reputation: 398
I am trying to write a simple raw query with Entity Framework to my database:
[ResponseType(typeof(Customer))]
[Route("name/{name}")]
[HttpGet]
public List<Customer> GetCustomerName(string name)
{
//var results = db.Customers.SqlQuery("SELECT Name from dbo.Customer").Where(p => p.Name.Equals(name)).ToList();
var results = db.Customers.SqlQuery("SELECT Name from dbo.Customer WHERE Name = @Name",new SqlParameter("@Name",name)).ToList();
//var results = db.Customers.Where(p => p.Name.Equals(name));
return results;
}
The last Entity Framework query works just fine, but I want to do raw SQL to get something back simple because I have never gotten raw SQL with Entity Framework to work and I see all of these examples where people says it works for them. The top 2 var results do not work I get this error any help would be greatly appreciated. I am a Web API 2 newbie and I am just trying to learn it
So the error says a member of the type AccrRevAcct
does not have a corresponding column. I am not sure what that means AccrRevAcct
is a column on my database but so is Name and I just want the Name of my customer.
http://localhost:61280/api/Customers/name/1st MIDAMERICA CREDIT UNION
This is the call I make to my server and like I said it returns fine with the 3rd statement but that isn't raw SQL like I want to achieve. I only want to do this because I have some developers saying they can't get everything to work in EF I personally like it and haven't ran into this problem I want to show them fine just drop to raw SQL, but I need to show them I can make it work first lol.
Upvotes: 0
Views: 426
Reputation: 5227
If you are using SqlQuery
on the DbSet
you have to return a full instance. If you want to reshape the data like you are doing you need to use the SqlQuery<T>
on the db.Database
instead.
Example from https://msdn.microsoft.com/en-us/library/jj592907(v=vs.113).aspx
using (var context = new BloggingContext())
{
var blogNames = context.Database.SqlQuery<string>(
"SELECT Name FROM dbo.Blogs").ToList();
}
Upvotes: 2
Reputation: 26
Honestly I think you are trying to solve the wrong problem. The last query is the one you should be using. If your developers can't get EF to work that is what needs to be fixed.
Upvotes: 0