I have 4 very big tables (round 1mil records in each).

I use linq to put them into the same class type then i union them in memory.

var t = table1.union(table2)

Is there anyway i can do union within the statement. Doing it in memory is to slow.

Any suggestions welcome

Union is not supported. Milllions of records to fetch at one time is not a recommended practice by all means.

What you need to do is to fetch them one by one into the same EntityCollection.

Why do you need to union 4 million rows?

The reason is I need to use a take page on a set of data.

I want to order the data by DateCreated then do a take page on that.

Therefore returning 100 rows of data. Then using the paging on my grid page to the next 100 rows using take page

You could create a view which UNIONS the 4 tables together, and then use LINQ to query that. Using Skip and Take in the LLBLGen linq provider will be translated to server side paging statements which will be run on the server, which should give acceptable performance.

Matt

Thanks for the responses.

View does seem to be the best route.