Sql – LINQ to SQL classes to the own classes

data-access-layerlinqlinq-to-sql

I'm looking at using LINQ to SQL for a new project I'm working on, but I do not want to expose the LINQ classes to my applications. For example, do an select in link returns a System.Linq.IQueryable<> collection. As well, all the classes generated to represent the database use Table, Column, EntityRef classes and attributes. It's fine if my data access layer has LINQ dependancies, but I don't want my application to.

So my thoughts are that I will have to use the LINQ to SQL generated classes as intermediate classes that are not exposed outside of my data access layer, and create my own classes which the application can use. What is the easiest/effecient way to get the data from the LINQ to SQL classes into my own classes?

Best Answer

I totally agree with your thinking - I would try to avoid exposing LINQ to SQL entities directly to the world.

I would definitely recommend using a "domain model" of your own, either a 1:1 mirror of the underlying LINQ to SQL entities, or a different one.

As long as you have a domain model that is quite similar to the underlying LINQ to SQL entities, you can use tools like AutoMapper to easily shuffle data between your LINQ to SQL entities and your domain model classes. It should be pretty easy and flexible to do it that way!

Related Solutions

.net – Entity Framework vs LINQ to SQL

LINQ to SQL only supports 1 to 1 mapping of database tables, views, sprocs and functions available in Microsoft SQL Server. It's a great API to use for quick data access construction to relatively well designed SQL Server databases. LINQ2SQL was first released with C# 3.0 and .Net Framework 3.5.

LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. As such, you can mix and match a number of different database vendors, application servers or protocols to design an aggregated mash-up of objects which are constructed from a variety of tables, sources, services, etc. ADO.Net Framework was released with the .Net Framework 3.5 SP1.

This is a good introductory article on MSDN: Introducing LINQ to Relational Data

C# – LINQ query on a DataTable

You can't query against the DataTable's Rows collection, since DataRowCollection doesn't implement IEnumerable<T>. You need to use the AsEnumerable() extension for DataTable. Like so:

var results = from myRow in myDataTable.AsEnumerable()
where myRow.Field<int>("RowNo") == 1
select myRow;

And as @Keith says, you'll need to add a reference to System.Data.DataSetExtensions

AsEnumerable() returns IEnumerable<DataRow>. If you need to convert IEnumerable<DataRow> to a DataTable, use the CopyToDataTable() extension.

Below is query with Lambda Expression,

var result = myDataTable
    .AsEnumerable()
    .Where(myRow => myRow.Field<int>("RowNo") == 1);

Best Answer

Related Solutions

.net – Entity Framework vs LINQ to SQL

C# – LINQ query on a DataTable

Related Topic