LINQ to SQL changes in .NET 4.0

People have been asking via Twitter and the LINQ to SQL forums so here’s a list I put together on a number of the changes made for 4.0.

*25 Aug 2009* – Updated with additional changes, some of which are new in beta 2.

Change list

Performance

  • Query plans are reused more often by specifically defining text parameter lengths (when connecting to SQL 2005 or later)
  • Identity cache lookups for primary key with single result now includes query.Where(predicate).Single/SingleOrDefault/First/FirstOrDefault
  • Reduced query execution overhead when DataLoadOptions specified (cache lookup considers DataLoadOptions value equivalency)

Usability

  • ITable<T> interface for additional mocking possibilities
  • Contains with enums automatically casts to int or string depending on column type
  • Associations can now specify non-primary-key columns on the other end of the association for updates
  • Support list initialization syntax for queries
  • LinqDataSource now supports inherited entities
  • LinqDataSource support for ASP.NET query extenders added

Query stability

  • Contains now detects self-referencing IQueryable and doesn’t cause a stack overflow
  • Skip(0) no longer prevents eager loading
  • GetCommand operates within SQL Compact transactions
  • Exposing Link<T> on a property/field is detected and reported correctly
  • Compiled queries now correctly detect a change in mapping source and throw
  • String.StartsWith, EndsWith and Contains now correctly handle ~ in the search string (regular & compiled queries)
  • Now detects multiple active result sets (MARS) better
  • Associations are properly created between entities when using eager loading with Table-Valued Functions (TVFs)
  • Queries that contain sub-queries with scalar projections now work better

Update stability

  • SubmitChanges no longer silently consumes transaction rollback exceptions
  • SubmitChanges deals with timestamps in a change conflict scenario properly
  • IsDbGenerated now honors renamed properties that don’t match underlying column name
  • Server-generated columns and SQL replication/triggers now work instead of throwing SQL exception
  • Improved binding support with the MVC model binder

General stability

  • Binary types equate correctly after de-serialization
  • EntitySet.ListChanged fired when adding items to an unloaded entity set
  • Dispose our connections upon context disposal (ones passed in are untouched)

Database control

  • DeleteDatabase no longer fails with case-sensitive database servers

SQLMetal

  • Foreign key property setter now checks all affected associations not just the first
  • Improved error handling when primary key type not supported
  • Now skips stored procedures containing table-valued parameters instead of aborting process
  • Can now be used against connections that use AttachDbFilename syntax
  • No longer crashes when unexpected data types are encountered

LINQ to SQL class designer

  • Now handles a single anonymously named column in SQL result set
  • Improved error message for associations to nullable unique columns
  • No longer fails when using clauses are added to the partial user class
  • VarChar(1) now correctly maps to string and not char
  • Decimal precision and scale are now emitted correctly in the DbType attributes for stored procedures & computed columns
  • Foreign key changes will be picked up when bringing tables back into the designer without a restart
  • Can edit the return value type of unidentified stored procedure types
  • Stored procedure generated classes do not localize the word Result in the class name
  • Opening a DBML file no longer causes it to be checked out of source control
  • Changing a FK for a table and re-dragging it to the designer surface will show new FK’s

Code generation (SQL Metal + LINQ to SQL class designer)

  • Stored procedures using original values now compiles when the entity and context namespaces differ
  • Virtual internal now generates correct syntax
  • Mapping attributes are now fully qualified to prevent conflicts with user types
  • KnownTypeAttributes are now emitted for DataContractSerializer with inheritance
  • Delay-loaded foreign keys now have the correct, compilable, code generated
  • Using stored procedures with concurrency no longer gets confused if entities in different namespace to context
  • ForeignKeyReferenceAlreadyHasValueException is now thrown if any association is loaded not just the first

Potentially breaking changes

We worked very hard to avoid breaking changes but of course any potential bug fix is a breaking change if your application was depending on the wrong behavior. The ones I specifically want to call out are:

Skip(0) is no longer a no-op

The special-casing of 0 for Skip to be a no-op was causing some subtle issues such as eager loading to fail and we took the decision to stop special casing this. This means if you had syntax that was invalid for a Skip greater than 0 it will now also be invalid for skip with a 0. This makes more sense and means your app would break on the first page now instead of subtlety breaking on the second page. Fail fast :)

ForeignKeyReferenceAlreadyHasValue exception

If you are getting this exception where you weren’t previously it means you have an underlying foreign key with multiple associations based on it and you are trying to change the underlying foreign key even though we have associations loaded.Best thing to do here is to set the associations themselves and if you can’t do that make sure they aren’t loaded when you want to set the foreign key to avoid inconsistencies.

[)amien

67 responses

  1. Avatar for 7alwagy

    Really!? I heard that Microsoft will no longer support LINQ2SQL and will put all the effort to the Entity Framework project, so what's going on?

    7alwagy 2 June 2009
  2. Avatar for Lucas

    Did you mean "This means if you had syntax that was invalid for a Skip greater than 0 it will now also be invalid for skip with a 0."? :)

    Will the code generation templates be available/editable as T4 files?

    Lucas 2 June 2009
  3. Avatar for Lucas

    By the way, good job! It's great to see MS hasn't entirely abandoned LINQ to SQL in favor of LINQ to Entities.

    Lucas 2 June 2009
  4. Avatar for KristoferA

    Great summary and great work. Thanks! Once again L2S takes the lead... :)

    KristoferA 2 June 2009
  5. Avatar for Damien Guard

    @Lucas: Yes, you're write on the valid/invalid typo I've now corrected that.

    There won't be T4 templates for LINQ to SQL in-the-box but I'll be continuing to update the LINQ to SQL T4 template on CodePlex at http://l2st4.codeplex.com which right now already includes all the forthcoming improvements listed in the Code Generation section :)

    Damien Guard 2 June 2009
  6. Avatar for Eric J. Smith

    Don't forget to check out the PLINQO templates as well. :-)

    Eric J. Smith 2 June 2009
  7. Avatar for Andrew

    No mention of the Database Triggers issue being resolved. Has this been addressed? I wrote a blog post detailing the problem and a workaround for it but it'd be nice to know if this is getting addressed.

    Andrew 3 June 2009
  8. Avatar for Andrew

    Sorry, my bad, seems this is being addressed (I missed "Server-generated columns and SQL replication/triggers now work instead of throwing SQL exception").

    Thanks!

    Andrew 3 June 2009
  9. Avatar for Liang

    I heard Linq-2-SQL is dead. Get a little bit confused.

    Liang 3 June 2009
  10. Avatar for Erik

    Yeah, I'm confused myself - I thought there wasn't going to be any further development occurring on L2S -- not that I'm complaining at all. Are you able to shed any light on this?

    Erik 3 June 2009
  11. Avatar for Mikeon

    What about just making dlinq usable?

    Current ver. and it seams v.next, both will have EntitySet which is NOT IQueryable.

    This prevents usage of linq2sql in any real life scenario.

    Consider:

    client.Orders.Where(o=>o.Total>1000); // runs linq to objects rather than linq to sql

    Same problem exists in entiy framework.

    Mikeon 3 June 2009
  12. Avatar for Damien Guard

    @Mikeon: EntitySet is the loaded in-memory collection for relationships.

    If you want all the orders for a specific client where the total is greater than 100 then you perform:

    db.Orders.Where(o => o.Client == client && o.Total > 1000);
    

    I don't see how this design or usage pattern makes it "unusable"

    Damien Guard 3 June 2009
  13. Avatar for Clarity

    I am confused too. WTF! Is MS going to support Linq to SQL in future or not? Can they make up their mind, seriously. It is getting annoying.

    Clarity 3 June 2009
  14. Avatar for Damien Guard

    We said we would improve the core of it and add customer requests where it makes sense but that Entity Framework would be the primary focus.

    If you heard otherwise it certainly wasn't from this team.

    Damien Guard 3 June 2009
  15. Avatar for TD

    Will it allow you to refresh the generated code to pick up changes you've made to the database schema?

    TD 3 June 2009
  16. Avatar for John Rusk

    @TD I believe this 3rd party tool offers a feature to do what you're asking for:

    @Mikeon

    To clarify Damien's point, you can write:

    from client in db.Customers
    let orders = db.Orders.Where(o => o.Client == client &amp;&amp; o.Total > 1000)
    where orders.Any()
    select client;
    

    BTW. This page covers some more advanced scenarios

    John Rusk 4 June 2009
  17. Avatar for Mikeon

    @Damien Guard

    Scenario: Suppose I have an application where customers log in, which gives me the customer object. When they want to view their orders from last month it seams natural to just do customer.Orders.Where since I already have the customer object. But that is exactly what i cannot do, since I will get all customer orders most of which I don't need.

    Another Imagine that i have a control that displays customer summary on which i have few details like customer.Name and total amout of money they spent (calculated from orders). Natural way would be to send a customer object into such control, but yet again, I cannot without performance issues.

    Maybe I'm overreacting here when I say it makes dlinq unusable. All of those problem can be handled as you suggest, but it makes customer.Orders property pretty useless in most cases, other than an entry point for adding new orders.

    EnitySet/EntityCollection should really allow for switching to IQueryable mode somehow. As it is now, there is no way to solve this from inside customer class since there is no access to the context, so we cannot have customer.QueryableOrders to make our lives easier.

    So maybe MS should consider providing a hook somewhere. Like EntitySet.AsQueryable() as an instance method - which should do the trick without huge implications on other parts of the solution. Making an option to make it default in the designer wouldn't hurt also.

    Also consider, that everything I said also relates to EF.

    Mikeon 4 June 2009
  18. Avatar for Eric

    Thanks for the update! Let's face it. LINQ to SQL isn't going anywhere...

    Eric 4 June 2009
  19. Avatar for Mikeon

    @John Rusk

    I can't use code you provided inside Customer class since context is not available there - which is of course good in most cases.

    Mikeon 4 June 2009
  20. Avatar for Abel

    Fantastic! Microsoft should never have stopped on Linq2SQL. It's further easier, stable, faster and better in some aspects than ESQL.

    I expect the project is not going to be discontinued and lead to new improvements and features!

    Thanks Microsoft and Linq2SQL team!

    Abel 4 June 2009
  21. Avatar for shawn

    +1 on Mikeon's comments, it seems natural to want/expect IQueryable relationships between entities to query off of the pre-definedrelations, rather than writing new queries from scratch. IQueryable EntitySets and/or access to the entity's active context would make the above scenarios possible, which I would agree are often preferable over writing a new query outside of the entity in order to just get a subset or projection off of an existing relation in LINQ2SQL or the EF.

    shawn 5 June 2009
  22. Avatar for Damien Guard

    @Shawn/Mike the problem is that EntitySet may be loaded into memory already so the IQueryable semantics would be different as they would be LINQ to Objects and not LINQ to SQL (so any query here would in fact load much more data out of the db than you wanted as we can't partially-load an EntitySet).

    We could bypass the in-memory instances and go to the database if the domain object had a reference to the context (it doesn't it has an IEnumerable lazy-loaded source provided) and then people are going to be very confused when their query with no where criteria returns a different selection to enumerating over it.

    I'm not convinced that passing domain/entity objects around as a query entry point is a particularly elegant solution...

    Damien Guard 5 June 2009
  23. Avatar for 5x1llz

    I am all for linq to sql as long as MEF is in beta and you don't really have anything slightly complex to deal with in your database. It's good to get up and running quickly however... the minute you have any issues it's hard to get around them. I see the list of updates coming is support of this statement.

    I also think microsoft should have focused on speedtracking MEF as opposed to wasting people's time with linq to sql just to make a big splash with Linq. There were so much products coming out around 3.0 I'm sure it was a marketing blitz and has only led to confusing the developers, jeopardizing the stability of applications who have adopted this only to find out it wasn't stable or that they may have to swtich to MEF eventually.

    It would have been better to just release a product at LEAST as mature as what is already out there.

    Again, I'm all for Linq to Sql when the situation is simple enough to excuse it's use.

    Doubt I'll be back plz reply on my blog or @5x1llz on twitter if your'e so inclined..

    5x1llz 6 June 2009
  24. Avatar for Mikeon

    @Damien Guard

    I' well aware of the reasons why MS decided against having IQueryable entities BUT!

    Afair, before RC1 they were IQueryable so obviously it was possible - though I imagine there were issues mentioned by you and on many other pages.

    as for: "(it doesn't it has an IEnumerable lazy-loaded source provided" Wouldn't hurt to change it to a lazy-loaded IQueryable source wouldn't it?

    Yet again, I'm well aware of the issue with query semantics - mergeing results - all this kind of stuff, but what you could do, is as I mentioned, provide us with IQueryable, protected member inside the entities - this, with proper naming, comments etc will not confuse people and I'm not convinced it's THAT hard to implement merging so we get same results all the time.

    "I'm not convinced that passing domain/entity objects around as a query entry point is a particularly elegant solution..." - well, sometimes it is, sometimes it isn't, but as I wrote previously it is not only that. Paging, filtering and all other kinds of calculations.

    Really try writing a real app with dlinq and you'll see the issue. Either you will end up with code dealing with data residing outside of objects owning the data or some other nasty hacks like decorators/adapters encapsulating both the contex.

    Just think about it. I know dlinq won't be fixed but at least try making EF correct.

    Mikeon 8 June 2009
  25. Avatar for shawn

    @Damien:

    It seems perfectly reasonable to try to traverse relationships on an object the same way whether you are "in" a query or accessing a relationship from withing an object property. That's the whole point of lazy loading and abstraction, right? Say you wanted to add a MostRecentOrderDate property on a Customer object. You have a denormalized database, and want to pull this date off of the most recent Order record in the database. Right now if you implemented this on the Customer entity (which I think is an entirely intuitive/reasonable/elegant approach) as:

    public DateTime MostRecentOrderDate
    {
        get { return this.Orders.OrderByDescending(o => o.OrderDate).Select(o => o.OrderDate); }
    }
    

    It causes a "SELECT *" query to load every Order into memory, even though you only wanted a single column value off of a single row. Say this is a standard property you want to show all over the place (and ideally to also use as part of a predicate in larger queries), you either have to denormalize the database (ie "bad" DB design), copy and paste the above query in-line all over the place (ie "bad" O/O design and violation of DRY), or stub out the property and make sure that it gets pre-calculated on the objects coming out of your repository for every single query that might possibly need it. Of course the latter adds significant complexity (especially if you aren't already using the repository pattern), requires prior knowledge of when each calculated property will be used (or extra expense by eager loading when it isn't used), and adds an increased risk of someone forgetting to pre-load the property when it is needed. I don't consider any of these as particularly elegant solutions for solving this type of common problem. If the relationship was IQueryable, or even if there was access to the active context associated with the entity to write your own queries against it (although that does violate the separation of persistence concerns, which may or may not be an issue), then it allows you to do something like the above simply and elegantly.

    @Mikeon:

    If you haven't already, you should make sure to leave comments on the EFDesign and ADO.NET blogs. I think it would be helpful for the EF team to hear your feedback, because I think what we are both asking for is an entirely reasonable and common use case.

    shawn 14 June 2009
  26. Avatar for Damien Guard

    @shawn: I wouldn't consider writing a query and hiding it behind what appears to be a simple property good practice.

    Let's say for a moment we go to the database every time to get this and display the MostRecentOrderDate on the screen. Below that we have a list of orders we loaded for this customer in order date format, descending.

    Apart from the overhead in refreshing that single property by running an entire query every time there is also the chance those two dates won't match up if they were queried at different times and a new order has been created.

    The orders collection on a customer is loaded just once into memory and will not change for the lifetime of the datacontext unless it is specifically refreshed. Having queries over this collection return completely different results to enumerating the result would be confusing and error-prone.

    If EntitySet were IQueryable and we had loaded 10 orders into memory and 5 more were subsequently added to the database what would be the effect of:

    return from a in myCustomer.Orders select a;
    

    15 or 10?

    Damien Guard 15 June 2009
  27. Avatar for shawn

    @Damien,

    Extending that line of thinking, it would be bad practice to "hide" the original lazy-loaded relationship query in the first place. I definitely understand some people preferring every database query to be explicit, hence the EF default of requiring .Load() before every relationship query (which of course is now becoming optional in v2 because not everyone wants to be forced to work that way). But in some cases it can reasonably be preferable to take the L2S approach of implicitly executing queries to load related data while traversing the object graph. It seems natural to extend that mechanism beyond just trivial foreign key relationships.

    The MostRecentOrderDate is obviously a simplified example, but abstracting away queries is a key purpose of an O/RM. Having a means of being able to write:

    return customers.Where(c => c.MostRecentOrderDate >= date);

    and have the framework unroll the query, instead of writing:

    return customers.Where(c => c.Orders.Any(o => o.OrderDate >= date));

    Being able to encapsulate little nuggets of domain logic directly in the actual domain objects allows an extra layer of abstraction that can make code more maintainable in certain scenarios. As you mention, it is possible to run into synchronization issues with lazy-loading data on long-lived data contexts at different times. But this is true when lazy-loading the current IEnumerable relationships, they may be out of sync in some way with the parent entity. C'est la vie.

    Computed Properties or Model Defined Functions in the EF may turn out to be just what I'm looking for. I'm just saying that I would love for the framework to be able to translate these types of queries into the database for me somehow, rather than having them stuck in the Linq2Objects ghetto. :)

    shawn 16 June 2009
  28. Avatar for Damien Guard

    As you point out things like

    return customers.Where(c => c.MostRecentOrderDate >= date);

    Just won't work - the reason for this is that the compiler has generated IL for the MostRecentOrderDate property and we can't reverse-engineer this at runtime into TSQL.

    Keep an eye on this blog for a solution to that and computed properties - I have a project I'm putting online soon that lets you write properties in such a way that the underlying LINQ provider can translate them - the same exact logic works on LINQ to SQL and LINQ to Entities as well as LINQ to Objects...

    Damien Guard 16 June 2009
  29. Avatar for Mikeon

    Why can't you make it explicit as i suggest? If i want IQueryable i should be able to use it in entities (or even outside if i need). Make it a QueryXXX method. Leave sub properties as IEnumerable and everyone is happy. Easy!

    Mikeon 17 June 2009
  30. Avatar for shawn

    W00t! A way to do computed properties that the LINQ provider can translate would be fantastic! :)

    shawn 22 June 2009
  31. Avatar for BrianP

    I second that, computed properties would be awesome! Please do post soon :-)

    BrianP 27 June 2009
  32. Avatar for Tom

    Will the designer support for LINQ-to-SQL in VS.NET 2010 support updates? So, for example a table is changed in the database (new fields are added or a type is changed), will these changes be reflected in the designer in VS.NET 2010 using a "refresh" button without having to "redo" dragging and dropping the table all over?

    Tom 30 June 2009
  33. Avatar for Damien Guard

    @Tom: Unfortunately not. There's more to it than just refreshing like how to preserve the user customization of the various properties/additional columns etc.

    Damien Guard 30 June 2009
  34. Avatar for Adam Smith

    One improvement I'd like to see is an option to trim char and nchar columns when they are deserialized. These columns are padded with spaces when they are deserialized, which makes working with them difficult in .NET code, especially when comparing them with user input (String.Equals does not ignore trailing spaces like ANSI sql does). To work around this I have to trim these strings when the entity class is loaded. However, my work-around cannot apply when a char or nchar column is used as a primary key, because LinqToSql will not recognize the key value if it is not padded with spaces.

    To give you an example, my table of aviation airspace fixes has an Identifier column of nchar[5], and the identifiers can vary from 3 to 5 characters in length. It would be less efficient to store these ids as nvarchar[5] just to work around this trimming issue.

    Thanks, Adam

    Adam Smith 2 July 2009
  35. Avatar for Andrew Jones

    "We said we would improve the core of it and add customer requests where it makes sense but that Entity Framework would be the primary focus.

    If you heard otherwise it certainly wasn't from this team."

    This is what I heard, but from previous experience this usually translates into "we'll maintain it as is, but don't expect any new functionality." I'm at a place where I would like to push a .NET platform (LINQ to SQL or Entitity Framework) as our preferred upgrade path at work, but I'm not sure if one of the options will be viable in the long term.

    In other words, if I haven't dealt with either previously should I skip LINQ to SQL and go straight to Entity Framework?

    Andrew Jones 7 July 2009
  36. Avatar for Steele Price

    How about IUpdatable Support? This is required for ADO.Net Data Services and would be EXTREMELY helpful. I had to implement this myself by modifying the T4 Templates. It would be great to have this support in the Box

    Steele Price 10 July 2009
  37. Avatar for azazeal

    At some point we'd like be able to navigate into the Linq2Sql designer by selecting a data-type (perhaps from a list) and not trying to figure out where we had seen that data-class last.

    Having like 300 atm in my project and it's really a pain to "find" the class in the designer in order to make the simplest of changes.

    azazeal 27 July 2009
  38. Avatar for Andrey Kuleshov

    Hi,

    I'm really glad that Linq To Sql still alive :)

    Is there any chance that Linq To Sql will support Full Text Indexes in SQL Server in some native way, without wrappers (user-defined functions)?

    Thanks

    Andrey Kuleshov 31 July 2009
  39. Avatar for Sean

    Are they adding support for "Bi-directional" serialization? Like what your template does?

    Sean 4 August 2009
  40. Avatar for Brian

    Any chance the L2S designer in VS 2010 will display the associations on each table? Similar to the EF designer. That would be a nice touch. Thanks!

    Brian 11 August 2009
  41. Avatar for Damien Guard

    I'm not sure what changes the designer has had but I don't believe that has been done. I'm trying to get a list from that team to incorporate here.

    Damien Guard 11 August 2009
  42. Avatar for John

    What about SQL spatial support? It's a huge pain to maintain a linqed table manually because it has a single field that is a spatial field. Nice to be able to just drag and drop the table, if you don't want to make support for that data type then just don't add it to the table's properties upon dropping.

    John 16 August 2009
  43. Avatar for Howard

    Many thanks for this post, and the work being put into LINQ to SQL - nice to hear LINQ to SQL has not been forgotten! EF is nice but too complex for simple projects.

    For me: "LinqDataSource now supports inherited entities" and ITable are fantastic (I used Connect to post a bug report on this last year) - at last I should be able use my modified datacontext with LinqDataSource (it returns an ITable variant called TableView )

    Howard 7 September 2009
  44. Avatar for eti

    Hi,

    One thing I've really needed ( for creating SqlCacheDependency objects ) is the ability to call GetCommand on a different instance of the DataContext than the one used to create the IQueryable.

    I see no point in not allowing this since i believe only the "metadata" from the context is used to create the command and not the actual values from the instance.

    Anyway good to know LINQ2SQL is still maintained.

    eti 8 September 2009
  45. Avatar for Jeswin

    Good to hear that Linq to Sql is actively improved. We recently moved a project from EF to L2S (yes!) due to performance issues.

    In some ways it is actually superior to EF due to its simplicity; if you give people simple tools they will figure out how to solve complex problems in ways you haven't imagined. (We are building inheritance support on top of Linq2Sql by modifying expression trees).

    Jeswin 22 September 2009
  46. Avatar for Peter Svahn

    Are there any plans to support many-to-many asscoiations in Linq to SQL?

    Peter Svahn 22 September 2009
  47. Avatar for Damien Guard

    Many-to-many relies on having an intermediate table in the database - LINQ to SQL supports that today but doesn't hide that fact and doing so would be counter to the table=class concept LINQ to SQL uses.

    It is possible to hide intermediate tables yourself - PLINQO does it through templates and could probably modify the L2ST4 templates to achieve a similar result.

    With response to the other questions about SqlCache, full-text indexes, spacial support etc. - if it's not on this list then you can assume it's won't be part of .NET 4.0.

    Damien Guard 22 September 2009
  48. Avatar for Chad Gross

    What about the ability to manage scope and visibility of mapped memebers? We go through alot of pain in a Domain'ish drvien design with this thing. A HUGE enhancement for us would be to allow us to control the scope of the getter vs. the setter. We always need the ability to make the setter internal and the getter public. A major pain is to rename the generated prop to [DBColumnName]Internal and then we manually add a new prop with the name [DBColumnName] as a public getter only.

    Chad Gross 20 October 2009
  49. Avatar for Damien Guard

    @Chad: There are no other changes except what is on this list.

    You could likely achieve what you want with templated code generation, check out L2ST4.

    Damien Guard 20 October 2009
  50. Avatar for luc

    Linq2SQL is a fine and usable piece of software. I agree 100% with Jeswin that Linq2SQL is superior to EF due to its simpler level of abstraction and the fact that ONE CAN BUILD ON TOP OF IT.

    Reliable, complex systems are build with simple concepts. Yes, we have started with EF(due to concerns surrounding L2S) and yes we wasted two development months.

    To me, as the name says, Linq2SQL gives the data layer of the application:

    • a one to one table to object mapping with translation between SQL and .Net types
    • and a reliable way to perform CRUD operations on the database.

    The problems arise when people try to use it as an object repository. This looks good only in demos. In real life, for any complex system you need to take control of your domain objects. To avoid things like "sorry, can't have the context to query from within your objects because it's not good for you".

    @ 5x1llz - and other people who think EF is the answer for large systems - On the contrary, L2S could be used to build the data layer of domains as complex as you can imagine. Of course, you have to build your own domain framework, which is not hard considering L2S can do the heavy-lifting regarding the DB access.

    At the other end, EF gives you a complicated, prepacked domain template that is not extensible - just try it out. EF gives you the illusion it can solve your domain but as you want to add abstractions into the framework more and more workarounds are needed to the point that a mess emerges. From what I have experienced so far EF is:

    • too complex for simple systems
    • impossible to extend for complex systems (I am referring to extending the framework to incorporate your own abstractions and conventions).

    This leaves EF for systems in the range of up to 50-100 tables, but don't get your hopes to high if you want to implement an elegant solution. Of course one can build a 200 tables system with EF (let's assume it, although I'm not sure about that as I ran in significant performance problems when approaching 100 tables; even following the MS recommendation to pre-compile the model views, moved the inefficiency from the start time to compile time - not to mention that that one table with over 70 fields had to be split to reduce the views compilation from 2.5 min to 40 sec). Just that it's not worth the pain and effort. You will spent far less time implementing your own domain as opposed to searching for workarounds that give you half baked solutions.

    The reality is that complex systems are architected with specific conventions built into the domain, otherwise a 200 tables+ system becomes needlessly hard to manage. From start, EF emphasizes configurations over conventions and comes up with huge XML configuration files, very hard to maintain (csdl, msl, ssdl). If you think that the designer takes care care of it and you don't have to open/modify them - good luck with that.

    IMO, EF continues the MS tradition of producing yet another data access library. As a matter of fact, I've read articles advising to use EF as a DAL! What?

    It is said that EF v2 will be better. I'm sure it will be improved considering the teamis more open to the comunity. But I am skeptical about being significantly improved as the starting point is very discouraging.

    All I know is what I tried: L2S v1 - usable; EF - v1 unusable. Kind of tired of smoke and mirrors.

    @ Damien - It seems to me that a large number of folks out there (including MS folks) hope that the L2S library lives up to its name and the really important sections will continue to be addressed:

    • Query stability and performance - I see you guys made progress - Thank you !
    • SQL to .Net data types translation - would you not consider this a core feature and add the new SQL types?
    • Is it possible that sometime in the future other databases will be considered?

    Thanks, Luc

    luc 28 November 2009
  51. Avatar for Damien Guard

    @Luc: I think you really should take a look at EF in .NET 4.0 - a great number of the issues were solved.

    LINQ to SQL will never get other databases - 85% of the codebase is the SQL Server provider.

    Damien Guard 28 November 2009
  52. Avatar for luc

    Thanks for advise Damien. Going live in a month so it won't cut it for us - VS 2010 launches in March, the earliest.

    Can you please answer the question regarding new SQL datatypes? Are they going to be supported in L2S at par with EF?

    Is the SQLServer provider used in LINQ the same with the one in EF? Thanks again

    luc 28 November 2009
  53. Avatar for Luc

    @ Damien

    Hi, The L2ST4 templates you've developed are really nice and useful.

    They work well against the dbml generated through VS.

    When running against a file generated with SQLMetal I get the following exception:

    System.ArgumentNullException: Value cannot be null.
    Parameter name: objectToConvert
    at Microsoft.VisualStudio.TextTemplating.ToStringHelper.ToStringWithCulture(Object objectToConvert)
    at Microsoft.VisualStudio.TextTemplatingE49A33EA5AEBE14AAF17271D723B1779.GeneratedTextTransformation.TransformText()
    

    Can you please have a look? Thanks, Luc

    Luc 11 December 2009
  54. Avatar for pita.o

    Hi Damien: Any chance you will have a way to attach a custom property type mapping delegate?

    eg for ...

    public class POCO { public Money MyMoney {get; set;} }

    can we attach a delegate on-type-map that says, if source type is decimal/double and target type is Money do ... assign new Money(value)?

    Otherwise does L2S have a simple globally factored way to achieve this?

    That will really help.

    pita.o 18 December 2009
  55. Avatar for Damien Guard

    There are no other changes other than what is described in this list.

    Adding such a feature would be of limited use as you would't be able to query on it. You could achieve by mapping the decimal as private then adding your own hand-written public MyMoney property that lazily-creates based on the private decimal and likewise updates it on set.

    Damien Guard 18 December 2009
  56. Avatar for Jerry Beers

    Can you talk more about the ITable interface and how to use it to improve mocking? Or point to an article that does?

    Thanks!

    Jerry Beers 4 March 2010
  57. Avatar for o_o

    So good to see that LINQ to SQL is not dead! We would have lost countless hours migrating to .NET 4.0 if not. Will mysql be supported?

    o_o 5 March 2010
  58. Avatar for Damien Guard

    LINQ to SQL will never support anything other than SQL Server & Compact Edition.

    Damien Guard 5 March 2010
  59. Avatar for andrew

    Any idea if it is now possible to "fiddle with" the sql, like adding OPTION (RECOMPILE) at the end of the SQL used in sp_executesql() ?

    andrew 13 April 2010
  60. Avatar for Damien Guard

    You can modify the TSQL if you have a specific point where you know you are going to execute the query - see my LINQ to SQL tips and tricks #2 post.

    Damien Guard 13 April 2010
  61. Avatar for fschwiet

    "LINQ to SQL will never get other databases – 85% of the codebase is the SQL Server provider."

    I was looking at EF for this exact reason, though I like linq2sql alot myself. I needed to interface with a MySQL database. What I found is that EF's MySQL support is pretty limited. It sounds like the problem is more related to the MySQL Connector software used to allow MySQL support (which is not Microsoft software). I'm just saying EF isn't necessarily going to solve people's cross database compatibility issues either.

    If only Linq to nHibernate were more complete, I'd be using nHibernate. I only experimented with fluent nHibernate some, but its was pretty sweet for setting up object/table mappings.

    My experience using linq2sql has been great. The main reason I can't use it in production environments is that the systems I work on use stored procs that return multiple tables, which is not supported natively by either linq2sql or EF.

    fschwiet 13 April 2010
  62. Avatar for Patrick

    No native support for dynamic linq was added (as ScottGu explains here)

    I was under the impression that the reason this feature wasn't in the first version was simply because you ran out of time. I guess I assumed that meant it would be in the next version!

    Extremely useful for ad hoc where clauses (the only time I ever really use it.)

    Patrick 14 April 2010
  63. Avatar for Damien Guard

    Dynamic LINQ isn't part of LINQ to SQL and it wouldn't be if it ever were to be added as it is used to build LINQ trees for any type of IQueryable provider.

    It's also quite specialized and advanced. I'd rather see the framework stay smaller and focused and have dynamic LINQ move into CodePlex - but I'm not on LINQ/.NET Framework any more so don't have any insider knowledge.

    Damien Guard 15 April 2010
  64. Avatar for Carl

    Damian, I like many people have found this blog post very useful. There are a large number of us that love Linq-to-sql and were very happy to see these improvements being added to .NET 4.0 .

    Many thanks for keeping us updated. You mention in your last post that you are not on the LINQ dev team anymore however your blog is the one that comes up when you do any searches for LINQ-to-SQL. Can you share with us any other blogs or places where we can find anymore information about LINQ-to-SQL, i.e. current developer blogs.

    Hopefully Microsoft have realised that there is widespread support for LINQ-to-SQL and will continue to develop it alongside Entity Framework.

    Carl 26 April 2010
  65. Avatar for Calvin

    Reading the comments above, It seems that a lot of people are confused about what L2S actually is... not sure why though... I think the name fully describes what it is. Linq to [MS]Sql. If you want other databases you would need to write linq providers for them, which could aptly be named, LinqToOracle, LinqToMySql, LinqToWhateverDataSourceYouWant...

    Why people expect MS to write products for competing vendors is beyond me.

    As for the comments surrounding EF, I agree with the general consensus that EF is about as good for data access, as a piece of feces is good for dinner! It is painfully slow, and the while the idea of abstracting your models for use with different databases is great, EF manages to bugger that up by relying on the clients having the models of the underlying databases! Imagine having 10 clients all using database X, and then trying to migrate to database Y. Instead of the abstraction happening in one place, it is happening on all 10 clients!! ALL of which now need to be updated!!! Obviously that only leaves EF useful as DAL, which in fact turns out to be horrible idea as the compilation is so horribly slow.

    Having said that however, I do agree with the approach of not automatically lazy loading data. This can lead to some rather interesting bugs, especially if the context gets disposed between queries.

    In certain scenarios where the lazy loading behavior is desirable, it can be easily achieved by extending your entity objects... ie. Remove original foreign key reference property, add your own property with a query in the get accessor to the other side of the partial class. Not exactly rocket science. I do realize it would be easier if the code was auto generated, but especially when it comes to auto generated code it is never a good idea to generate code that can cause confusing bugs for developers.

    Calvin 30 April 2010
  66. Avatar for Adam

    If you are going POCO, is it possible for ICollection or IList to resolve to an EntitySet when querying data so then we get some kind of lazy loading support.

    I think everyone thinks EF should be great, but it's too much work for alot of applications. Okay, LINQtoSQL is never going to be LINQtoMySQL or LINQtoOracle... But adding dynamic LINQ into the library as standard, making many-to-many references easier... Okay technically someone might feel that it isn't LINQ or LINQtoSQL... But the point is that those features would help developers be productive and in my opinion niether of those features contaminate what you're trying to do. If anything it makes SQL Server more attractive to businesses because when you write applications you don't need the complexity of EF, but you're getting most of the benefit.

    If it'll help, with due consideration give the people what they want!

    I remember when C# was just released and alot of people asked about optional parameters. I remember seeing someone say, it'll never happen and I was kind of glad at the time. I had the same fear about 'dynamic' keyword too, thats for another time. Now we've got optional parameters, I don't have to write loads of overloads, I quite like it.

    I shouldn't of been so closed minded.

    I guess it re-inforces that C# and .NET is there to do a job, it's not a religion.

    Adam 11 June 2010
  67. Avatar for Arman

    I'm currently working with Entity Framework and there are things that we need to do but can't without an ugly hack or directly querying Stored Procedures. If Microsoft is trying to enforce "good practice" it's not working. So we ended up having a mix of ADO .Net, Entity Framework and now I'm looking into LINQ to SQL (I love it!). Finally MS had something that made writing data centric apps as easy as in FoxPro. But wait... FoxPro is closing to its end also.

    Arman 22 July 2010