3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Srb1313711
  • 2,017
  • 5
  • 24
  • 35
  • 4
    *"this is getting all data from the DB table and then filtering down to the one row"* no, it's not. Entity Framework will build a `SELECT * FROM tbl1 WHERE name = 'Robin Banks'` query and pass that to the database server, which will execute it as a normal query and return the appropriate rows to Entity Framework. – Albireo Dec 15 '15 at 09:55
  • 1
    You are wrong. It will filter in database not in memory. – Giorgi Nakeuri Dec 15 '15 at 09:55
  • It will only filter in memory if you are doing something like this: `context.tbl1.ToList().where(obj=>obj.name="Robin Banks")`. Because you first load everything in the memory and then filter the data with the where() statement. But like it is done by you the filter is applied on the database. – croxy Dec 15 '15 at 09:56
  • Cheers guys this is what I wanted to hear, I couldnt find anywhere that told me this? Can you provide for me some links to which you found this info? – Srb1313711 Dec 15 '15 at 09:57
  • 2
    What you're looking for is [LINQ to Entities](https://msdn.microsoft.com/en-us/library/bb386964(v=vs.110).aspx). – Albireo Dec 15 '15 at 09:58

3 Answers3

5

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

Rohit
  • 10,056
  • 7
  • 50
  • 82
2

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.

theyetiman
  • 8,514
  • 2
  • 32
  • 41
1

you are not sure if an item with a given key exists --> FirstOrDefault

Entity Framework 4 Single() vs First() vs FirstOrDefault()

Community
  • 1
  • 1
J4ime
  • 145
  • 6