Coffeehouse Thread

10 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Entity Framework and SQL Server 2012

Back to Forum: Coffeehouse
  • User profile image
    W3bbo

    I see SQL Server 2012 (formerly codenamed 'Denali') has finally adopted a SQL syntax for result paging with its OFFSET and FETCH keywords.

    Of course, server-side paging with SQL Server has been possible for years (such as the standards-compliant SELECT subquery approach, or TOP/ROWCOUNT and ROW_NUMBER in earlier versions of SQL Server).

    However, MySQL has had this functionality for many years - and at least since 2004 when I first started looking for information about paging. MySQL's LIMIT keyword is certainly more succinct than Microsoft's and at first I was slightly disappointed when I saw they didn't adopt the same syntax, but then I realised how MySQL's LIMIT statement is rather hackish (there's no way to specify you want all rows after index x returned unless you specify an insanely large number).

    But this is mere academic discussion. Now comes the point of this thread: will the ADO.NET Entities Framework be updated to include support for OFFSET/FETCH, and if so... when?

    Also, does anyone have any performance and benchmarking information on OFFSET/FETCH compared to previous techniques (especially that which EF's .Skip().Take() approach used)?

  • User profile image
    cbae

    I'm still waiting for spatial data type support. The ADO.NET team indicated that after .NET 4.5 is released (which is presumably when EF 4.5 will RTW), they are planning to release major updates to EF out of band with .NET. This should result in quicker turn around for support of new features like this.

    As for the LINQ syntax, I wouldn't expect there to be any deviation from the current method names. The point of LINQ is to abstract out specific behaviors of the underlying data source using common syntax.

  • User profile image
    figuerres

    , W3bbo wrote

    I see SQL Server 2012 (formerly codenamed 'Denali') has finally adopted a SQL syntax for result paging with its OFFSET and FETCH keywords.

    Of course, server-side paging with SQL Server has been possible for years (such as the standards-compliant SELECT subquery approach, or TOP/ROWCOUNT and ROW_NUMBER in earlier versions of SQL Server).

    However, MySQL has had this functionality for many years - and at least since 2004 when I first started looking for information about paging. MySQL's LIMIT keyword is certainly more succinct than Microsoft's and at first I was slightly disappointed when I saw they didn't adopt the same syntax, but then I realised how MySQL's LIMIT statement is rather hackish (there's no way to specify you want all rows after index x returned unless you specify an insanely large number).

    But this is mere academic discussion. Now comes the point of this thread: will the ADO.NET Entities Framework be updated to include support for OFFSET/FETCH, and if so... when?

    Also, does anyone have any performance and benchmarking information on OFFSET/FETCH compared to previous techniques (especially that which EF's .Skip().Take() approach used)?

     

    my guess is that EF will probably not. why?  becasue they fail to do a number of simple things they could do and the story i hear is "we can't be SQL server specific"  and or other things like that.

    some days i wish that EF would be more like Linq to SQL  as Linq to SQL handles a number of simple common things much better than EF does.

    I like most of what EF does, just wish they would cater a bit more to SQL server some times.

  • User profile image
    vesuvius

    @figuerres: What I have learnt recently (or shall I say" come round to thinking") is to view programming wholistically a little better. I needed some scheduling in my application, mission critical of sorts, no tolerance for error. It turns out Java has a quartz library that has been ported to .NET. Within minutes I was up and running, with some very nicely thought out API's that are a joy to use.

    The same goes for nHibernate. That ORM has been used in countless projects, and most people speak about highly it because it generally is a well conveived project. If you learn to take the best from what is available, then people should accept that Microsoft made a mistake with Entity Framework, where they should have just given Anders the time, money and support to further develop the Linq ORM. I would never use EF out of choice, and would opt for nHibermate, because that is the correct descision for ORMs in .NET (If I need something beefier than Linq to SQL). I see that decision as clearly as the difference between night and day now.

    That is not to say the everything in Java is great, but it is time people make informed choices rather than being automatons.

  • User profile image
    W3bbo

    @vesuvius: Part of it is tooling - I find the tooling for EF (i.e. the designers and stuff in VS) to be the bigger timesaver than the (better designed?) codebase in nHibernate.

    I'll admit that it has been three years since I last used nHibernate properly - how good is it now?

  • User profile image
    blowdart

    , W3bbo wrote

    @vesuvius: Part of it is tooling - I find the tooling for EF (i.e. the designers and stuff in VS) to be the bigger timesaver than the (better designed?) codebase in nHibernate.

    I'll admit that it has been three years since I last used nHibernate properly - how good is it now?

    If you're going to look at it, then look at Fluent nHibernate, because otherwise it's just a mess of configs and is the most frustrating thing ever. I hate nHibernate with a passion, the Fluent version means I just dislike it.

    Now Code First EF is really interesting ... Julie Lerman just completed a new book on it too.

  • User profile image
    cbae

    @blowdart: I like the EF Code First story because it eliminates the need to reference the entity data model resources (*.csdl and *.msl) in your connection strings (yuck!).

    The problem is that there are a bunch of different technologies built on top of EF and EF CF that I like, but I have to piece them together to be really productive.

    First, I can't pull myself away from designing the physical schema first in SSMS. The table and diagram designers in SSMS allow me to be way more productive than the entity data model designer in VS. The ability to have multiple table designs open (if MS ever decides to eliminate the MDI option in SSMS, somebody's going to get choked), the cut-and-paste support for column definitions, and the ability to create relationships visually through the diagram designer make SSMS the preferred option for starting any project for me.

    Since I start with database first, I need to reverse engineer it for EF CF. The EF Power Tools VS add-in is a great tool for this. It creates all your entity classes, and creates the map classes (derived from EntityTypeConfiguration<T>) using a fluent API (i.e. no need for referencing the *.csdl and *.msl resources from the entity data model). HOWEVER, the reverse engineering tool uses what appears to be some internal code generation template that you have no access to. The template uses a HashSet<T> collection for child entities. HashSet isn't supported by WP SDK, so I can't use those entity classes in a portable class library. So, I still have to use an entity data model and add a "code generation item" (i.e. T4 template) from the entity data model. Since the template file is added to the project, I can modify it to use List<T> instead of HashSet<T>, which allows me to then added the generated classes to a portable class library. The entity data model stays in its own utility project that's used only for the purpose of generating the entity classes. This project isn't reference by any other project.

    Oh, and because the EF Power Tools are still CTP, there are some things that it doesn't reverse engineer correctly. So I have to go through the map classes and manually tweak them, if needed. For example, by default all string properties are mapped as nvarchars, so I have to manually modify those mappings to add .IsUnicode(false) for those cases in which I use a regular varchar.

    So what I end up with is kludge of entity classes generated from an entity data model using a T4 template that I have to manually tweak and EF CF map classes (reverse engineered directly from the database using the EF Power Tools) which I also have to tweak.

    I've also been playing around with EF Migrations, which is a framework with a fluent API used for migrating EF CF changes back to the database. It's a really cool framework, but this, like the EF Power Tools, is about 95% there (i.e. it doesn't allow you to specify names of foreign key or primary key constraints), which seems to be the upper limit for any tool Microsoft comes up with.

    *sigh*

  • User profile image
    cbae

    I just found a code generator that does exactly what I want. Boy, do I feel silly now. LOL.

  • User profile image
    TudorT

    SQL Server 2012 is final, RTM, but still no support in EF5/ADO.NET for OFFSET/FETCH, as far as I can see.. Probably the two teams and MS should start talking to each other.. Smiley

  • User profile image
    exoteric

    Is there a list of issues with Entity Framework somewhere? It's possible that not all constructs need to be supported directly as long as the mapped SQL can be dealt with efficiently by SQL Server.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.