CODEWITHSUNDEEP
c#linqentity-frameworkstored-procedureslinq-to-entities
Scott Silvi
Scott Silvi

Reputation: 3109

Multi tenant EF implementation w/ Stored Procedures

When using SProcs in EF4, I understand the concept of mapping views to entities, then using function imports to map my set/update/deletes to sprocs. The question I have is how this applies to multi tenant architecture. Consider the following scenario:

We have several hundred customers utilizing our multi-tenant database/application. Each customer has somewhere between 50-200 Accounts in the Accounts table. If I expose a view to EF, I cannot parameterize that view. So the following line: 

query = (from e in context.Accounts select e).where(e => e.companyID = 1)
[forgive me if I'm syntactically incorrect. still learning EF!]

,by definition, would have to return all of the Accounts first, then filter using my wear clause. is this correct? I can't imagine how else the process would work.

Am I missing something here?

Upvotes: 0

Views: 347

Answers (2)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364279

That is the difference between Linq-To-Objects and Linq-To-Entities. Linq-To-Objects operates on IEnumerable<T> and you pass delegates to its methods to define the query which will be executed in the memory. Linq-To-Entities operates on IQueryable<T> and you pass expressions to its methods do define expression tree which is transformed by Linq-to-entities provider into another syntax - to SQL!

So your query will be executed in the database and filtering will be done in the database as well. Be aware that after executing commands like AsEnumerable, ToArray, ToDictionary or ToList you transform the rest of the query to Linq-to-objects.

If you write the query on the result of stored procedure execution you are always doing Linq-to-objects only querying ObjectSets directly forms Linq-to-entities queries.

Upvotes: 3

Mitch Wheat
Mitch Wheat

Reputation: 300559

EF shouldn't be bringing all the accounts back first and then filtering. Rather, it should be be emitting a query with a WHERE clause.

You can check using SQL Profiler, just to be 100% sure.

Upvotes: 2

Related Questions