I'm trying to understand whether it possible to get a single row from a database using entity framework without returning all the data. Maybe I'm misunderstanding how EF work but I believe its similar to the following:
Id | Name | Place 1 | Teressa Green | UK 2 | Robin Banks | Germany 3 | Liam Neeson | Canada
If I want
Robin Banks Id do something similar to
context.tbl1.where(obj => obj.name = "Robin Banks")
However from what I've understood this is getting all data from the table and then filtering down to the one row. Is there a way to return just the one row back to the logic without initially returning all the data?
To put the my issue in one sentence. I'm trying to avoid loading back all rows when I just want 1.
This line will not actually execute anything on the database:
context.tbl1.Where(obj => obj.name == "Robin Banks")
It will return an
IEnumerable<tbl1> which is going to be lazily evaluated when you come to use it. To execute an actual query on the database you need to perform an enumeration on the
IEnumerable<tbl1> (e.g. a
.SingleOrDefault()). At this point EF will convert your
Where() clause into actual SQL and execute it on the database, returning the specified data. So, it will get all data that matches your predicate
obj.name="Robin Banks". It will not get all the data in
tbl1 using a SQL statement and then filter the results in .NET - that's not how it works.
However, you can do this (if you need to, but not recommended almost 100% of the time) by first enumerating with
context.tbl1.Where(obj => <some SQL evaluated expression>).ToList()
And then adding an additional predicate on the end:
context.tbl1.Where(obj => <some SQL evaluated expression>).ToList().Where(obj => <some .NET evaluated expression>).ToList()
You can log the actual SQL being generated by EF by doing the following with your context:
context.Database.Log = Console.WriteLine;
And see for yourself what's going on under the hood.
I think you need to use here
var result= db.yourtable .SingleOrDefault(c=>c.Name== "Some Name");
Whenever you use SingleOrDefault, you clearly state that the query should result in at most a single result