none
System.Data.Linq.Table vs System.Collections.Generic.List RRS feed

  • Question

  • Hi Guys,

    Not sure when to use what and what is the advantage of using which one.

    I am writing LINQ to SQL and which is preferable to use for return the data from the method:

    public static List<BRULE> GetBusinessRules()
            {
                MyDataContext dc = new MyDataContext();
                return dc.GetTable<BRULE>().ToList<BRULE>();
            }

    VS

    public static System.Data.Linq.Table<BRULE> GetBusinessRules()
            {
                MyDataContext dc = new MyDataContext();
                return dc.GetTable<BRULE>();
            }

    Wednesday, April 17, 2019 10:01 PM

Answers

  • LINQ to SQL has been dead for years now. Do not use it in your code. Support is still there because it is hard to remove features from the framework but no investment is being made. Entity Framework is the MS-supported approach for this going forward. So in regards to your question you'd never use System.Data.Linq. This is even more important now that .NET Framework 4.8 is the last version of NF. .NET Core does not support LINQ to SQL so building an app using it will simply make it harder to migrate to NC in the future.

    As for whether you should use LINQ-like tables, DataTables or business objects then I think the general consensus is to use pure business objects (e.g. List<T>). This allows you to evolve your data layer in the future without exposing the data technology being used. For example you might start with LINQ to SQL but returning List<T>. Then as you move from LINQ to SQL to EF or another ORM (or even pure ADO.NET) you can make those changes (even piecemeal) without breaking existing code outside the data layer.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Aryaa Thursday, April 18, 2019 2:25 PM
    Thursday, April 18, 2019 2:06 PM
    Moderator

All replies

  • Not sure when to use what and what is the advantage of using which one.

    https://dzone.com/articles/reasons-move-datatables

    https://www.codingblocks.net/programming/boxing-and-unboxing-7-deadly-sins/

    Wednesday, April 17, 2019 11:09 PM
  • Hi,

    Thank you for your reply.

    But I am not referring to System.Data.DataTable.

    Referring to System.Data.Linq.Table<MyDataModelType>

    Are both having the same cons which are mentioned in the links you provided..

    Thursday, April 18, 2019 1:45 PM
  • LINQ to SQL has been dead for years now. Do not use it in your code. Support is still there because it is hard to remove features from the framework but no investment is being made. Entity Framework is the MS-supported approach for this going forward. So in regards to your question you'd never use System.Data.Linq. This is even more important now that .NET Framework 4.8 is the last version of NF. .NET Core does not support LINQ to SQL so building an app using it will simply make it harder to migrate to NC in the future.

    As for whether you should use LINQ-like tables, DataTables or business objects then I think the general consensus is to use pure business objects (e.g. List<T>). This allows you to evolve your data layer in the future without exposing the data technology being used. For example you might start with LINQ to SQL but returning List<T>. Then as you move from LINQ to SQL to EF or another ORM (or even pure ADO.NET) you can make those changes (even piecemeal) without breaking existing code outside the data layer.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Aryaa Thursday, April 18, 2019 2:25 PM
    Thursday, April 18, 2019 2:06 PM
    Moderator
  • Hi Michael Taylor,

    You saved me!!

    Thanks a lot for the quick reply.

    So could you please confirm that I should be moving forward with Entity Framework for my DataAccess class right. I am starting a project coding which is long term. I hope EF is the right way to go forward with.

    Thank you.

    Thursday, April 18, 2019 2:25 PM
  • Ultimately I think you'll need to make that decision on your own. The general recommendation is to use EF because it makes it a lot easier to write data access code without having to think about the underlying structure. However there are pros and cons to every technology so you should evaluate whether EF (or any other ORM) fits your needs or not. I think starting with EF is a good thing and fall back if it doesn't work out. Some things to consider when using EF (or any ORM).

    1) EF will force you to follow some rules around how your data is set up that may go against what you want. This includes things like a default constructor, accessibility of property getter/setters, how navigation properties are implemented, etc. Some of these constraints are mitigated in newer versions of the framework. EF Core (which you can use in NF if needed) has a different set of rules so you might look into that as well.

    2) EF really wants to control the lifetime of your data so if you have a basic CRUD app then EF works well. However if you have complex relationships and/or run disconnected you may find yourself writing more code to work around EF.

    3) EF works at the table/view level and therefore (at least in the full version) requires your table structure to be set up in a "relational" way. This generally means most queryable tables need a PK and relationships are managed using FK. If your database has a non-standard setup then EF will be harder to use.

    4) If your database relies mostly on stored procedures to interact with it (common in older systems) then EF is going to be less beneficial. EF wants to query (and manipulate) tables directly. If you have to go through sprocs to do this then EF can do it but you are losing the benefits of EF. ADO.NET might be better at this point.

    5) EF has overhead in both initialization and runtime. At startup EF wants to create your database for you so you generally have to disable that unless you want database migration support. It also has to find and build the relationships you've defined. This slows down startup. When making query calls EF wants to track everything by default so it creates proxy objects and tracks your changes. If you are mostly a read only system then this overhead is wasted so you need to disable it which is added work.

    6) EF can end up exposing itself to your higher level code. There is debate whether ORM-agnostic code is needed or not. This is an entirely different conversation that sparks heated discussions. Let's just leave it at the fact that if you decide to use EF and you allow the DbContext/IDbSet and related EF-specific objects to bubble outside your data layer then it becomes harder later on to switch to something else (generally not an issue) but also to maintain the existing code. For example your (non-data) code has to be aware of whether an object is attached to the context or not when making updates. In my experience this greatly complicates the higher level code while providing little to no benefit. However if you don't allow it to "bubble up" to higher level code then you have to write even more code to wrap the EF stuff and handle the translation which means more code to maintain.

    7) Relationships are automatically handled in EF and that may be a bad thing. Setting up relationships in EF is as simple as adding a property. However you need to understand how EF represents relationships otherwise you may end up adding duplicate records to child tables and whatnot.

    EF (like any ORM) has a learning curve. I believe it is a good place to start but it isn't for every project. In the apps I manage we use EF for the core data and ADO.NET for anything that doesn't fit into EF nicely or that doesn't need the overhead. I'm in the "don't bubble EF outside the data layer" camp so I wrap everything. More code to maintain but the rest of the apps (which are far larger) are easier to manage.


    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, April 18, 2019 2:42 PM
    Moderator
  • I didn't realize you were talking about Linq-2-SQL, which is still a viable solution at this time that is better than dataset and datatable . :)
    Friday, April 19, 2019 1:08 AM
  • Hi Michael Taylor,

    You saved me!!

    Thanks a lot for the quick reply.

    So could you please confirm that I should be moving forward with Entity Framework for my DataAccess class right. I am starting a project coding which is long term. I hope EF is the right way to go forward with.

    Thank you.

    EF is a viable ORM, but it is slow  And there is Dapper a Micro ORM that is faster than EF, if you're looking for speed.

    https://exceptionnotfound.net/dapper-vs-entity-framework-vs-ado-net-performance-benchmarking/

    Friday, April 19, 2019 1:18 AM