none
When to use ADO and when not to. RRS feed

  • Question

  • There are many ways that we can access databases.We have linq to sql,linq to objects,linq to datasets and finally linq to ado.net.So many methods,but when is each supposed to be used??Also please highlight the main differences between all of these.
    • Moved by Bob Beauchemin Thursday, June 17, 2010 5:36 PM Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    Thursday, June 17, 2010 3:35 AM

Answers

  • Here's a quick breakdown, mostly chronologically.

    ADO.NET "classic" gives developers two models for accessing data.  The first is the reader model, where you execute queries and process the results in a forward-only read-only fashion.  The second model is the DataSet model, which allows you to cache results for off-line sorting, searching, filtering, etc.  The DataSet model also supports tracking and submitting changes.  Plus, DataSets can be easily stored as XML to write to (and read from) a file, to transport between application tiers (Web and WCF services).  Both models expect the user to understand the database's query language (T-SQL for the SQL Server crowd) and schema (table names, column names, etc.), and to process rectangular resultsets (rows and columns).

    The term LINQ to Objects is often used interchangeably with LINQ itself.  LINQ allows you to use a SQL-like syntax to query objects in memory. 

    LINQ to DataSets allows you to use the power of LINQ to query your DataSet.  (For reasons not worth discussing in this thread, the DataSet didn't lend itself well to LINQ and needed to be explicitly extended in .NET 3.5 to support LINQ.)  Prior to LINQ, developers could query the DataSet in various ways, but many developers wanted to be able to execute more database-like queries against the DataSet, like joins and aggregate queries.  That functionality is now available via LINQ to DataSets.  LINQ to DataSets first requires that you retrieve all of the data you're looking for into a DataSet.  This is a helpful and powerful feature, but this is not a substitute for intelligent access to your database.  In other words, it's generally a bad idea to retrieve the entire contents of your database into a DataSet and then rely on LINQ to DataSets.  As LINQ to DataSet is basically an extension of the DataSet model, I still consider that to be an available option when following the ADO.NET "classic" approach rather than saying developers need to make a choice between ADO.NET "classic" and LINQ to DataSet.

    I mentioned before that LINQ can query objects in memory, but LINQ also includes interfaces that allows components to tell LINQ "I'll examine the query and handle it myself". 

    LINQ to SQL supports this approach so that you can construct LINQ queries against your classes and LINQ to SQL will convert the query to SQL, execute the query, and return the results based on your object model.  LINQ to SQL was also included in .NET 3.5 and allows developers to developers to create application models, map those models to their database schema via code attributes.  LINQ to SQL also supports tracking changes, so you can easily submit pending changes back to your database.  LINQ to SQL supports SQL Server and SQL Server Compact, and is built on top of ADO.NET.  There are third party software developers out there who have built similar technologies to query other databases, but there is no provider model for LINQ to SQL.

    When you say LINQ to ADO.NET, I'm assuming you're talking about the ADO.NET Entity Framework.  The Entity Framework (or EF) supports LINQ and in many ways looks like LINQ to SQL.  You have your application's object model, which is mapped to your database model via mapping files (rather than attributes).  You can construct LINQ queries, which EF converts to the database's query language and returns results to you based on your object model.  EF provides support for more robust mapping scenarios than LINQ to SQL (such as handling many-to-many mappings without forcing you to add the "link" table to your object model).  EF is also built on top of ADO.NET.  However, unlike LINQ to SQL, EF has a provider model so that third party software developers can extend their ADO.NET providers to support the Entity Framework.  I'm not aware of any providers that support EF 4, but there are companies out there that support the initial release of EF for Oracle, Sybase, DB2, MySQL, Firebird, and many other back-ends.  EF also includes many query capabilities beyond just LINQ.  EF has its own text-based query language called Entity SQL that you can use to construct queries to return objects or to return results using the ADO.NET "classic" reader model.

    Which approach is right for you?  That really depends on what type of application you want to build and how you tend to think about data access.  If you'd rather focus on your object model and not think about SQL, I'd recommend at least looking at the Entity Framework.  If you prefer dealing with rows and columns via DataReaders and DataSets and want full control over any and all queries that are executed against your database, then working with ADO.NET "classic" directly might be more your speed.  While the Entity Framework is not a strict superset of LINQ to SQL, I see the Entity Framework as being more powerful, more feature reach and (between LINQ to SQL and EF) the technology that Microsoft is investing more into carrying forward (based on new functionality in .NET 4), so I wouldn't recommend starting new projects with LINQ to SQL unless you've already made major commitments involving that technology.

    I hope this information proves helpful.


    David Sceppa
    Thursday, June 17, 2010 8:19 PM
    Moderator
  • Rohan,

    LINQ is an incredibly powerful technology but it's not always the right tool for the job.  There may be times in your application where you want to change the structure of your query based on user input, whether that's using a different sort order for the results, completely changing the filter, or changing the type of data being returned.  To help you handle those types of scenarios, ObjectQuery gives you two options for supplying query information in the form of a string.  You can construct the entire query as a string using Entity SQL.  You can also use the query builder methods to supply individual pieces of the query via methods like Where, OrderBy, Select, etc.  (I think the examples here are helpful:  http://msdn.microsoft.com/en-us/library/bb896238.aspx).

    Whether you're using LINQ to Entities or the ObjectQuery class directly, you're generating query results in the form of objects.  If you're not really interested in the objects and want to just read the raw data out of a stream (to construct portions of a web page, perhaps) you can execute Entity SQL queries using the EntityClient provider.  This approach works just like getting a DataReader from any other ADO.NET provider.  There's a Connection class, a Command class and a DataReader class.  (Yes, there are Parameter and Transaction classes, too.)  I wouldn't recommend starting with this approach, but it is helpful for the times where getting full-blown objects back feels like unnecessary overhead.

    As you've pointed out, there are a lot of options, and I can understand how all of the options may seem confusing or even overwhelming, but there is some rhyme and reason to them all.  I hope this sheds a little more light on the topic for you.


    David Sceppa
    Friday, June 18, 2010 6:47 PM
    Moderator

All replies

  • Here's a quick breakdown, mostly chronologically.

    ADO.NET "classic" gives developers two models for accessing data.  The first is the reader model, where you execute queries and process the results in a forward-only read-only fashion.  The second model is the DataSet model, which allows you to cache results for off-line sorting, searching, filtering, etc.  The DataSet model also supports tracking and submitting changes.  Plus, DataSets can be easily stored as XML to write to (and read from) a file, to transport between application tiers (Web and WCF services).  Both models expect the user to understand the database's query language (T-SQL for the SQL Server crowd) and schema (table names, column names, etc.), and to process rectangular resultsets (rows and columns).

    The term LINQ to Objects is often used interchangeably with LINQ itself.  LINQ allows you to use a SQL-like syntax to query objects in memory. 

    LINQ to DataSets allows you to use the power of LINQ to query your DataSet.  (For reasons not worth discussing in this thread, the DataSet didn't lend itself well to LINQ and needed to be explicitly extended in .NET 3.5 to support LINQ.)  Prior to LINQ, developers could query the DataSet in various ways, but many developers wanted to be able to execute more database-like queries against the DataSet, like joins and aggregate queries.  That functionality is now available via LINQ to DataSets.  LINQ to DataSets first requires that you retrieve all of the data you're looking for into a DataSet.  This is a helpful and powerful feature, but this is not a substitute for intelligent access to your database.  In other words, it's generally a bad idea to retrieve the entire contents of your database into a DataSet and then rely on LINQ to DataSets.  As LINQ to DataSet is basically an extension of the DataSet model, I still consider that to be an available option when following the ADO.NET "classic" approach rather than saying developers need to make a choice between ADO.NET "classic" and LINQ to DataSet.

    I mentioned before that LINQ can query objects in memory, but LINQ also includes interfaces that allows components to tell LINQ "I'll examine the query and handle it myself". 

    LINQ to SQL supports this approach so that you can construct LINQ queries against your classes and LINQ to SQL will convert the query to SQL, execute the query, and return the results based on your object model.  LINQ to SQL was also included in .NET 3.5 and allows developers to developers to create application models, map those models to their database schema via code attributes.  LINQ to SQL also supports tracking changes, so you can easily submit pending changes back to your database.  LINQ to SQL supports SQL Server and SQL Server Compact, and is built on top of ADO.NET.  There are third party software developers out there who have built similar technologies to query other databases, but there is no provider model for LINQ to SQL.

    When you say LINQ to ADO.NET, I'm assuming you're talking about the ADO.NET Entity Framework.  The Entity Framework (or EF) supports LINQ and in many ways looks like LINQ to SQL.  You have your application's object model, which is mapped to your database model via mapping files (rather than attributes).  You can construct LINQ queries, which EF converts to the database's query language and returns results to you based on your object model.  EF provides support for more robust mapping scenarios than LINQ to SQL (such as handling many-to-many mappings without forcing you to add the "link" table to your object model).  EF is also built on top of ADO.NET.  However, unlike LINQ to SQL, EF has a provider model so that third party software developers can extend their ADO.NET providers to support the Entity Framework.  I'm not aware of any providers that support EF 4, but there are companies out there that support the initial release of EF for Oracle, Sybase, DB2, MySQL, Firebird, and many other back-ends.  EF also includes many query capabilities beyond just LINQ.  EF has its own text-based query language called Entity SQL that you can use to construct queries to return objects or to return results using the ADO.NET "classic" reader model.

    Which approach is right for you?  That really depends on what type of application you want to build and how you tend to think about data access.  If you'd rather focus on your object model and not think about SQL, I'd recommend at least looking at the Entity Framework.  If you prefer dealing with rows and columns via DataReaders and DataSets and want full control over any and all queries that are executed against your database, then working with ADO.NET "classic" directly might be more your speed.  While the Entity Framework is not a strict superset of LINQ to SQL, I see the Entity Framework as being more powerful, more feature reach and (between LINQ to SQL and EF) the technology that Microsoft is investing more into carrying forward (based on new functionality in .NET 4), so I wouldn't recommend starting new projects with LINQ to SQL unless you've already made major commitments involving that technology.

    I hope this information proves helpful.


    David Sceppa
    Thursday, June 17, 2010 8:19 PM
    Moderator
  • Well i delved into the entity framework model(as it did seem the best choice).But i found there are 3 methods to implement EF

    1.Linq to Entities

    2.Entity SQL

    3.Query Builder

    Well all of them can be used to achieve the same result as per http://msdn.microsoft.com/en-us/library/bb896321.aspx

    What i cant understand is why there are so many methods?Are there any limitations to these methods?

    Friday, June 18, 2010 10:24 AM
  • Rohan,

    LINQ is an incredibly powerful technology but it's not always the right tool for the job.  There may be times in your application where you want to change the structure of your query based on user input, whether that's using a different sort order for the results, completely changing the filter, or changing the type of data being returned.  To help you handle those types of scenarios, ObjectQuery gives you two options for supplying query information in the form of a string.  You can construct the entire query as a string using Entity SQL.  You can also use the query builder methods to supply individual pieces of the query via methods like Where, OrderBy, Select, etc.  (I think the examples here are helpful:  http://msdn.microsoft.com/en-us/library/bb896238.aspx).

    Whether you're using LINQ to Entities or the ObjectQuery class directly, you're generating query results in the form of objects.  If you're not really interested in the objects and want to just read the raw data out of a stream (to construct portions of a web page, perhaps) you can execute Entity SQL queries using the EntityClient provider.  This approach works just like getting a DataReader from any other ADO.NET provider.  There's a Connection class, a Command class and a DataReader class.  (Yes, there are Parameter and Transaction classes, too.)  I wouldn't recommend starting with this approach, but it is helpful for the times where getting full-blown objects back feels like unnecessary overhead.

    As you've pointed out, there are a lot of options, and I can understand how all of the options may seem confusing or even overwhelming, but there is some rhyme and reason to them all.  I hope this sheds a little more light on the topic for you.


    David Sceppa
    Friday, June 18, 2010 6:47 PM
    Moderator