Get single row using Entity Framework without getting all data

.net c# entity-framework

Question

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:

TBL1

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.

1
2
12/15/2015 11:04:45 AM

Accepted Answer

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 foreach, .ToList() or .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 .ToList():

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.

1
12/15/2015 10:06:15 AM

Popular Answer

I think you need to use here SingleOrDefault

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



Related Questions





Related

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