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 handles ~ 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 deserialization
  • 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

SQL Metal

  • 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

80 responses  

  1. 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?

    7alwagyJune 2nd, 2009
  2. 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 – June 2nd, 2009
  3. 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 – June 2nd, 2009
  4. Great summary and great work. Thanks! Once again L2S takes the lead… :)

    KristoferAJune 2nd, 2009
  5. pingback

    […] one of the people on the LINQ to SQL team, just posted a list of 40+ changes that are coming for LINQ to SQL for .Net 4.0. Looking through the list, most of the items are bug fix items and not feature […]

    ThinqLinq - LINQ to SQL enhancements for 2010June 2nd, 2009
  6. @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 :)

    [)amien

    Damien GuardJune 2nd, 2009
  7. Don’t forget to check out the PLINQO http://plinqo.com templates as well. :-)

    Eric J. SmithJune 2nd, 2009
  8. 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.

    AndrewJune 3rd, 2009
  9. 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!

    AndrewJune 3rd, 2009
  10. I heard Linq-2-SQL is dead. Get a little bit confused.

    Liang – June 3rd, 2009
  11. 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?

    ErikJune 3rd, 2009
  12. 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.

    MikeonJune 3rd, 2009
  13. @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”

    [)amien

    Damien GuardJune 3rd, 2009
  14. 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 – June 3rd, 2009
  15. 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.

    [)amien

    Damien GuardJune 3rd, 2009
  16. Will it allow you to refresh the generated code to pick up changes you’ve made to the database schema?

    TD – June 3rd, 2009
  17. pingback

    […] LINQ to SQL changes in .NET 4.0 – Damien Guard talks about the changes and improvements that have been made to LINQ to SQL in the upcoming .NET 4.0 release […]

    Reflective Perspective - Chris Alcock » The Morning Brew #361June 3rd, 2009
  18. @TD I believe this 3rd party tool offers a feature to do what you’re asking for: http://www.huagati.com/dbmltools/

    @Mikeon

    To clarify Damien’s point, you can write:

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

    BTW. This page covers some more advanced scenarios: http://www.albahari.com/nutshell/linqkit.aspx

    John RuskJune 4th, 2009
  19. @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.

    MikeonJune 4th, 2009
  20. Thanks for the update! Let’s face it. LINQ to SQL isn’t going anywhere…

    Eric – June 4th, 2009
  21. @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.

    MikeonJune 4th, 2009
  22. 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 – June 4th, 2009
  23. +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 – June 5th, 2009
  24. @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…

    [)amien

    Damien GuardJune 5th, 2009
  25. 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 – June 6th, 2009
  26. @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.

    MikeonJune 8th, 2009
  27. pingback

    […] is why Damien Guard, a member of the LINQ to SQL / Entity Framework team, posted a list of changes to LINQ to SQL coming with the release of .NET […]

    Tony and Zuzana’s World » LINQ to SQL: It Lives! (sort of)June 9th, 2009
  28. pingback

    […] Damien has a nice post about the LINQ to SQL changes in .NET 4.0. […]

    Summary 29.05.2009 – 10.06.2009 « Bogdan Brinzarea’s blogJune 10th, 2009
  29. @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 – June 14th, 2009
  30. @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?

    [)amien

    Damien GuardJune 15th, 2009
  31. @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 – June 16th, 2009
  32. 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…

    [)amien

    Damien GuardJune 16th, 2009
  33. 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!

    MikeonJune 17th, 2009
  34. pingback

    […] For some time did not read anything about LINQ To SQL, and the truth is that I had raised was going with this extension of LINQ in Visual Studio 2010 and. NET 4.0 … well, the fact is that work has continued in LINQ To SQL and brings a lot of developments. NET 4.0. You can access all the news in this link. […]

    LINQ to SQL changes in VS 2010 & .NET 4.0 « WiseCodes : Bringing You The Power of Code !!June 18th, 2009
  35. pingback

    […] LINQ to SQL changes in .NET 4.0 […]

    Herding Code 50: Damien Guard on LINQ to SQL, Entity Framework, and Fontography | Herding CodeJune 18th, 2009
  36. W00t! A way to do computed properties that the LINQ provider can translate would be fantastic! :)

    shawn – June 22nd, 2009
  37. I second that, computed properties would be awesome! Please do post soon :-)

    BrianP – June 27th, 2009
  38. 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 – June 30th, 2009
  39. @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.

    [)amien

    Damien GuardJune 30th, 2009
  40. 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 – July 2nd, 2009
  41. “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 – July 7th, 2009
  42. 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 PriceJuly 10th, 2009
  43. pingback

    […] Publicado no blog do Damien Guard, um dos desenvolvedores do LINQ to SQL e do Entity Framework, a lista de mudanças que estão sendo feitas no LINQ to SQL para o novo .NET Framework, abaixo coloco uma versão traduzida da lista, o post completo pode ser lido lá no blog do Damien: http://damieng.com/blog/2009/06/01/linq-to-sql-changes-in-net-40 […]

    O que muda no LINQ to SQL com o framework .NET 4.0? | Thiago MontiniJuly 23rd, 2009
  44. 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.

    azazealJuly 27th, 2009
  45. pingback

    […] whatever the future may hold, DamienG has a blog post that outlines the changes in LINQ to SQL for .NET 4.0. Many interesting and important additions, but unfortunately despite all the whining, there is […]

    Heikniemi Hardcoded » LINQ to SQL changes in .NET 4.0July 28th, 2009
  46. 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 – July 31st, 2009
  47. Are they adding support for “Bi-directional” serialization? Like what your template does?

    Sean – August 4th, 2009
  48. pingback
    Learning resources for C# 4.0 and .NET 4.0 new features « Bogdan Brinzarea’s blogAugust 6th, 2009
  49. 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 – August 11th, 2009
  50. 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.

    [)amien

    Damien GuardAugust 11th, 2009
  51. 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 – August 16th, 2009
  52. pingback

    […] Changes in .NET 4.0 List of the changes made to LINQ to SQL to .NET 4.0 including some possible breaking changes […]

    LINQ to SQL resources » DamienGAugust 24th, 2009
  53. 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 )

    HowardSeptember 7th, 2009
  54. 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.

    etiSeptember 8th, 2009
  55. 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).

    JeswinSeptember 22nd, 2009
  56. Are there any plans to support many-to-many asscoiations in Linq to SQL?

    Peter Svahn – September 22nd, 2009
  57. 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.

    [)amien

    Damien GuardSeptember 22nd, 2009
  58. I guess linq2sql isnt dead :) Whohoo!

    I just hope that this is finally intergrated:
    http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=963657&kbln=en-us

    It never made it into the 3.5 framework, and its been a known problem for a long time. The hot fix fixed the problem. Its been available for years now.

    Travis Whidden – October 13th, 2009
  59. 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 GrossOctober 20th, 2009
  60. @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.

    [)amien

    Damien GuardOctober 20th, 2009
  61. 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 – November 28th, 2009
  62. @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.

    [)amien

    Damien GuardNovember 28th, 2009
  63. 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 – November 28th, 2009
  64. @ 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 – December 11th, 2009
  65. 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.oDecember 18th, 2009
  66. 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.

    [)amien

    Damien GuardDecember 18th, 2009
  67. 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 – March 4th, 2010
  68. 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 – March 5th, 2010
  69. LINQ to SQL will never support anything other than SQL Server & Compact Edition.

    [)amien

    Damien GuardMarch 5th, 2010
  70. pingback

    […] In addition to improvements with the Entity Framework, LINQ to SQL with .NET 4 also includes a bunch of nice improvements.  […]

    Visual Studio 2010 and .NET 4 Released - ScottGu's BlogApril 12th, 2010
  71. 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 – April 13th, 2010
  72. 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.

    [)amien

    Damien GuardApril 13th, 2010
  73. “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 – April 13th, 2010
  74. No native support for dynamic linq was added (as ScottGu explains here: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx)

    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.)

    PatrickApril 14th, 2010
  75. 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.

    [)amien

    Damien GuardApril 15th, 2010
  76. 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 – April 26th, 2010
  77. 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 – April 30th, 2010
  78. 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 – June 11th, 2010
  79. 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 – July 22nd, 2010
  80. pingback

    […] doubt since that “Update on LINQ to SQL and LINQ to Entities Roadmap” came out. However, it got a massive update in .net 4.0, despite concerns. I think it even got support for SPARSE columns in a later […]

    Not Rocket Science » LINQ to SQLFebruary 9th, 2013

Respond to this