Is there a possibility in the near future provide a SQL Server Compact 4.0 DB to synchronize with SQL Server, unfortunately, offers some of the SyncFramework for this version at no SyncCeProvider .... and the opportunity to work with a local cache async one is also forbidden, because it is supported only in version 3.5.

Unfortunately, I am clueless and if needed an alternative offered by the DB once support for the Entity Framework and the Local Cache Agent.

The version of SQL Compact 3.5 would work but unfortunately I have the problem that the auto-increment columns are not generated ..... too bad someone has an alternative suggestion?

** SUGGESTION **
Native support for query serialization and deserialization (to XML) decoupled from a concrete ObjectContext, including query parametrization and correct management of lambda expressions (e.g. using expression expansion as in LinqKit).

** WHY **
We've developed a set of classes that allow a query to be written in the client layer and executed in the service layer via web services (with POCO entities). After the query is written normally using linq to entities and obtaining an IQueryable<T> object, this implementation transparently does the following:

CLIENT side:
- Expands the query expression tree to correctly manage lambda expressions.
- Parametrizes the query decoupling it from constant parameter values (in order to cache the query independently from it's parameters concrete values).
- Serializes the query to XML.
- Compresses the serialized query (optionally).
- Sends the query to the server (web service request).

SERVER side:
- Open the concrete ObjectContext.
- If not in cache, decompress and deserialize the parametrized query and put it in the cache, else get the already decompressed and deserialized query from cache. Caching is needed to prevent deserialization as much as possible, because expression deserialization is expensive (fortunately serialization is not).
- Deparametrize query (replace parameters with actual values).
- Execute the query.
- Return the resulting objects to client side (web service response).

This proved to be very useful in many projects and, In order to achieve it, a lot of effort is required, but it probably will become obsolete in EF 5.

Regards,

When trying to load related entities using the "Include" eager loading feature of EF, it always generates SQL that uses an LEFT OUTER JOIN e.g.

from m in Customer.Includes("Orders")

will always generate the SQL:

SELECT ......
FROM Customers
LEFT OUTER JOIN Orders....

This is not always desirable, especially when you only want to retrieve Customers that have at least one or more related Orders. By forcing the generated SQL to always use LEFT OUTER JOIN, it's going to load all Customers regardless of whether they have related Orders or not. This can become extremely inefficient, especially when there are lots of Customers and you only want to load the few that have related Orders.

Would be good to have the option of using either an INNER or OUTER JOIN when doing eager loading to improve the generated SQL's efficiency.

LINQ-to-Entities should offer an anaogous operator to AsEnumerable() but instead of materializing results on the client, it would materiailze results on the server (e.g. a temp table for SQL Server), allowing subsequent operators to re-use intermediate results without risking additional server recomputation or requiring a round-trip from client to server.

The underlying requirement is to make it easier to build and maintain performant LINQ queries that use millions of rows, many complex joins, I/O intensive calculations like running totals or self-joins, or all of the above.

For these kinds of queries in T-SQL, a good way to get predictable performance is to materialize expensive intermediate results into a temp table, and then join the temp table to later parts of the query.

For example, imagine a query on a table of running totals, where first you need to self-join to turn running totals into single-period values, and then you want to do other aggregation on those single-period values, like grouping by store or salesperson. In that case, you get a more efficient query by ensuring that the server materializes results from the self-join rather than re-running the self-join multiple times. Of course, it would be great if the DBMS materialized automatically, but most modern DBMS's (including SQL 2008 R2) aren't usually that smart.

But today in LINQ it's impossible to materialize those intermediate results without a round-trip to the client. Such a round-trip is OK when the intermediate results are only a few rows, but for million-row intermediate results it's not workable.

Another case where server materialization is desirable is when building libraries of queries that can be re-used. You don't want "downstream" query writers to end up crafting hugely expensive queries against underlying tables, so you can pre-process those tables into cheaper temp tables, and then hand those temp tables out from your library. This ensures a predictable cost for each query.

Note that a temp table, not a table variable, should be used on SQL Server for materialization. This is because temp tables support parallel execution plans while table variables do not (at least on SQL 2008 R2 and lower). Without a parallel plan, most expensive queries run 4x or more slower on modern DB hardware.

Support extension methods to be defined for entities or generic extensions. As long as the extension returns an IQueryable or expression that only includes supported operations/extensions, then the provider should expand the extension method and replace it in the expression tree with the the returned iqueryable/expression.

Thus for the most part, any extension method encountered in the expression that is not supported, should be called to return an expression that can be inserted into the expression tree. So long as the process operators recursively as it is expanding the expression, eventually the expression tree will consist of supported expressions and from there the existing expression translator can be used.

An example of such a visitor is here:
http://stackoverflow.com/a/10942043/84206

There are some problems with this implementation though. For example the requirement to use AsExpandable and AsQueryable to ensure the IQueryable version and not Ineumerable version is called. Where as supported extension methods like .Where do not require you to call .AsQueryable first, and the runtime automatically understands that it should not use the IEnumerable extension method because it is being translated into an expression. All attempts I have made to determine how this "magic" occurs for supported extensions, so that I could implement similar magic for custom extensions, has lead to dead ends or someone telling me it is a mysterious "implementation detail".

Now that EF is open sources though, perhaps I will be able to investigate this further.