3

I have a Contact table in SQL Server, I mapped the table using Entity Framework in my C# console application:

EmployeeId  FirstName   LastName
--------------------------------
1           Ram         Kumar
2           Mohan       Raj
3           Aravind     Swaamy
4           Ajay        Kumar
5           Ram         Raj

I need to fetch the records of 1, 3, 5 (EmployeeId)

Currently I'm doing in the following way

List<Employees> emp = new List<Employees>();

var emp1 = dbContext.Employee.Find(1);
var emp3 = dbContext.Employee.Find(3);
var emp5 = dbContext.Employee.Find(5);

emp.Add(emp1);
emp.Add(emp3);
emp.Add(emp5);

Kindly assist me how to get list of records using Find method by passing a list of primary key values.

Some people thinking the question is duplicate of How to do an "in" query in entity framework?. No it's absolutely wrong. My question is how to fetch multiple records using Find extension method. Because IN operation perform slower than the Find operation.

Community
  • 1
  • 1
B.Balamanigandan
  • 4,713
  • 11
  • 68
  • 130
  • @CodeCaster: IN operation perform slower than .find() did you know this? – B.Balamanigandan Jan 30 '17 at 10:34
  • You can't do that..If you only load a few object use a IN statement. If you load hundred/thousands of object the best way to do it is using a join. Unfortunately you can't join a DbSet with an in memory table so you'll have to write a stored proc (and of course you'll lose object tracking) – George Vovos Jan 30 '17 at 10:41
  • I think this is a valid question... Using find will enable you to leverage the EF cache... So it's advantageous over a IN/Contains call... Of course, assuming the parameters are PKs for your entity. It seems very reasonable that EF should support a way to lookup and cache multiple entities by PK in a single database call. – Jmoney38 Nov 03 '17 at 20:21

1 Answers1

10

My question is how to fetch multiple records using FIND extension method.

You can't; Find() finds one record, which is not what you want.

Because IN operation perform slower than the FIND operation.

No, it's not. Your current code does this:

SELECT * FROM Employee WHERE EmployeeId = 1
SELECT * FROM Employee WHERE EmployeeId = 3
SELECT * FROM Employee WHERE EmployeeId = 5

In three database roundtrips. That is by definition slower than this:

SELECT * FROM Employee WHERE EmployeeId IN (1, 3, 5)

If the data count is large, the WHERE clause perform very slow than FIND. This is fact.

No, it's not. Find() is not SQL. It translates to WHERE PK_COL=value for any amount of primary key columns and values for one record. The reason Find() has a params parameter is because of compound primary keys.

So, the duplicate I linked to before does answer your question and does exactly what you want:

var primaryKeys = new int[] { 1, 3, 5 };

var employees = dbContext.Employee.Where(e => primaryKeys.Contains(e.EmployeeId));

Which translates to the WHERE ... IN shown above.

Community
  • 1
  • 1
CodeCaster
  • 147,647
  • 23
  • 218
  • 272