Temp table in LINQ to Entities

I have a table of user开发者_如何学JAVA ids that is huge.

I have an array of user ids that I want.

I have two tables that have a foreign user id key.

What is the best way to get this information performance wise?

Ideally in SQL the final result would be somewhat like this (null values if there is no user ID in one table but not the other):

userid  table1value table2value
1        null          12
5        123           null

As far as I'm aware, LINQ to Entities doesn't have a way to create a temp table on the fly... I think the closest thing would be to use LINQ to Objects with a class or generating an anonymous type.

Or, do it in a stored proc, and import the proc into the object context, and have your code call the proc. The proc can create a temp table, or whatever it wants.

HTH.

Well, LINQ allows you to join collections (entities) much like SQL does, so you could craft a few outer joins to get the data, and that SHOULD translate to a relatively efficient SQL statement when given to Entity Framework. Here's a small example:

var results = from u in UserDataSource
   join t1 in Table1DataSource on u.UserId = t1.UserId into gj
   from jt1 in gj.DefaultIfEmpty()
   select new {u.UserId, Table1Data = jt1 == null ? String.Empty : jt1.Data};

If the tables really are huge, or there's additional processing to perform to get the result, I would consider creating a view on the server and map it to an Entity in code. Then it becomes a very simple select; you just filter the view results to the user ids you want to see.

I was having the same issue last year and then when it came to performance, then instead of taking temp table for holding records (application end or service end) O moved to LINQ to SQL and made a stored procedure and called it through entities like LINQ to stored procedure =). And my stored procedure did all and returned only those records that I wanted and I did paging to at the stored procedure end so that I got limited data at a time rather than all data at application.

Do let me know if you need any help in that. I will give a code snippet too.