Exactly as the question asks. I am using entity framework for most of my code, but i also need to execute and return a count or columns from a sql table that is not within my entity framework context.
2 Answers
You can run a raw query using Entity Framework, for example:
using (var context = new BloggingContext())
{
var blogNames = context.Database.SqlQuery<string>(
"SELECT Name FROM dbo.Blogs").ToList();
}
If you want to return a more complex type, you can define your own class and use that instead. As long as the properties match the names of the columns you select, it will work. So lets make a class:
public class MyClass
{
public int Id { get; set; }
public string UserName { get; set; }
}
And use it:
List<MyClass> result = ctx
.Database.SqlQuery<MyClass>("SELECT Id, UserName FROM dbo.Users")
.ToList();
3 Comments
Gillardo
But how would you return multiple columns? All different data types?
DavidG
You can make your own non-entity class and use it. Check my edit.
Gillardo
Problem is that I cannot make a class because the table is dynamic and i do not know this at design time. I do have the metadata of the column names at runtime
The whole point of an IDataReader is to Cursor through the data as it is being returned over the wire (exempli gratia TCP/IP) and thus not incur the overhead of stuffing it all into memory. If you're dealing with millions of rows, then reading everything into memory is a bad idea. If that is the case, then grab the underlying connection string from EF API and utilize ADO.NET. There's better better Aysnc support there as well.
SqlConnectionwith your connection string?