Entity Framework with SQL Server 2000 (APPLY Operator) issue

Question

Hello,

I have a simple Linq query below:

var seq = (from n in GetObjects() 
          select n.SomeKey)
          .Distinct()
          .Count();

This query works find with SQL Server 2005 and above.

But, this start to give headache when I hooked the EF to SQL Server 2000. Because EF is using APPLY operator which only SQL Server 2005 and above can be supported. I do not know why the hell EF is using APPLy operator instead of sub queries.

My current work around is:

var seq = (from n in GetObjects() 
              select n.SomeKey)
              .Distinct()
              .ToList()
              .Count();

But, I can forsee more problems to come. The above query is just a simple one.

Did anyone come across such issue? And how you guys work around it? Or is there a way to force EF not to use APPLY operator?

Any help will be very much appreciated.

How Lun.

Answer 1

I met another obstacle, which return TOP(1) of a list. I couldn't use the method above to solve this query because my list can be 10,000 records long.

I don't think this can be solved. I am so fed up with EF.

I will move back to LINQ to SQL. So so disappointed with EF.

Answer 2

The problem is that you generated your model against a 2005 (or higher) DB. So the GUI designer put a ProviderManifestToken value of 2005 or 2008 into the EDMX. This causes the SQL Server provider to generate SQL optimized for those versions. To fix this:

1. Right click your EDMX file.
2. Open with XML editor.
3. Search for ProviderManifestToken
4. Change to 2000
5. Save and run.