LINQ and SQL performance issue when working with Membership

I am using ASPNET membership with 50000 records, 开发者_如何转开发and we have another table called "scm_Users" which has exactly number of records, they are NOT linked by any key. I have a simple SQL:

select * from dbo.aspnet_Users a, dbo.scm_Users b
where a.UserName = b.UserName

I can get 50000 records in less than 1 second.

In LINQ, (using Entity Framework) when I am trying to do the same:

IEnumerable<MembershipUser> allMembershipUsers = Membership.GetAllUsers().Cast<MembershipUser>();
ObjectQuery<User> users = this.DataContext.UserSet;

var    result = (from a in allMembershipUsers
                      from b in users
                      where a.UserName == b.UserName
                 select new 
                 {
                   ..... 
                 }).AsEnumerable();

When I binded the result to Grid, it got timed out for 50000 records. When I debugged it and I moused over the "result view", it got timed out too. Of course, if I use Membership.FindUsersByName() and limit the number of records, it will return the results peoperly.

And, if I bind 50000 records directly to the Grid without querying on Membership, Grid works too.

 var    result = (from b in users
                  select b).AsEnumerable();

What did I do wrong?

N.B.

What is Membership? It looks to me like you pulling from 2 different data sources, which will load both of them into memory and do the work on the sets in memory. You are also using casts a lot on what appear to be rather large datasets.

Try something more like this:

var result = from a in DataContext.MembershipUsers
             join b in DataContext.UsersSet on a.UserName equals b.UserName
             select new { ... };

One significant difference is that you are streaming 100K records instead of 50K. The SQL query will combine the two results on the server and return the 50K rows of data joined between the two tables. In your example, you are pulling down the 50K via GetAllUsers and the other 50K via UserSet. The catch is that unless you query the aspnet Membership table directly via your DataContext, you will not be able to avoid this.

I'd say it's the Cast that's causing the slowdown, not the query. Try it without the cast and get the raw entities back to see if that speeds things up.