Do you put Linq2SQL queries all over the place or in dedicated DAL classes?

Question

I have always inserted my Linq2SQL queries all over the place, in almost every class all over the place.

I woud like to know what your strategy about where to put your Linq2SQL queries?

Do you put them in separate datalayer classes or do you store them where they are used all over the place?

I think that I need to chang my strategy for Linq2SQL queries and store them into separate DataLayer classes. I think that it is a must if I am to be able to do TDD efficiently and to comply with Dependency Injection and the Solid principles..

Answer 1

If you are thinking of your LINQ queries as being LINQ2SQL queries you slightly missing the point.

You have LINQ queries. Your business layer accesses the data layer by making LINQ queries on the data layer (a datacontext). LINQ2SQL is the component which allows LINQ queries to access SQL Server.

Thats a serious oversimplification but the general point is if you hide all your LINQ away from the business layer your not really benefiting from its reason to exist.

If LINQ2SQL doesn't allow you to abstract your DB schema to the degree that you like then you should consider using the Entity Framework instead.

Answer 2

but inserting Linq2SQL queries into my "Customer" business object, then that class becomes much harder to test (unit tests) and create mock classes for. Also I think that is a violation of the SOLID (Single reason to change rule).

Answer 3

Putting all my Linq2SQL queries into a separate class makes it easy to replace it with a "mock/stub" when testing the business objects that access it.

Or am I wrong?

Answer 4

I completely wrapped all my LinqToSQL calls into a single DAL. My Website and Business Layers have no knowledge of the persistence framework I'm using. This way, if LinqToSql really does die or if I decide I want to use a whole new framework I don't have to hunt down all the places I made DB calls.

It also helps with reusability. I can use the same Business or DAL in other projects that use the same database.

Answer 5

LINQ is a language construct. All it requires is that your DAL expose your entities as IEnumerable or IQueryable and you can use LINQ against it. Your DAL could be based on LINQ2SQL or LINQ2Entities or your own custom code -- as long as it exposes your entities properly. You get some advantages, like delayed query execution if you use LINQ2SQL, but it's not strictly necessary. I see no point in avoiding the use of LINQ outside of the DAL. If I want to replace the DAL with something else not LINQ2SQL-based, I can. As long as I maintain the interfaces that the LINQ-based code expects I'm ok.

EDIT: The bottom line for me is that until they hit the DAL, they aren't LINQ2SQL queries, they're just LINQ. LINQ isn't going to disappear out of the language unless it's replaced with something better. The thing that makes it LINQ2SQL is that the DAL is implemented with LINQ2SQL. The rest of my code doesn't know (or care) that this is so. It could be LINQ2Objects or LINQ2Entities or ...

Answer 6

• I use external mapping.
• The domain classes are in a seperate assembly.
• The datacontext is in a helpers assembly.
• I have a number of xml mapping files

When I need access to the database I instantiate the Helpers.DataContext, with a connection string (which database) and a mapping file (which tables to map).

This keeps a nice seperation of concerns.

Answer 7

I personally create the "base" query in and expose the IQueryable.

In my business objects, I like to return concrete results, so I usually make the QueryByExpression style method internal only, and then create method stubs where I can pass in my criteria, and in the method I process the query, get the results from the database, and return an IEnumerable of the results, not exposing the rest of the app to need the context, or anything.

But, like many of the people said above, I would have to agree -- exposing your IQueryable to your code isn't horrible, it just makes it so you are a little more tied to your Linq2Sql stuff.

If you don't mind creating business objects that expose methods, but does not expose the IQueryable type, I feel that's better, but its more tedious to setup and configure your Business Object. Its also easier to test I feel, but that's just me. Make for re-usability also, and if you fix a bug, it doesn't exist in 30 different spots in your code 2 years later.

Just my 2cents.

Answer 8

I wouldn't necessarily create separate business objects simply because that is the point of LINQ2SQL and you would be re-creating a lot of what makes LINQ2SQL so great.

I would however recommend creating a class library with static classes with your L2SQL code in it. This gives you the advantage of being able to replace single assemblies to change your business logic. Additionally if you have extra methods for accessing data, your static classes are a good place for that logic.

I would however agree with Florian if your needs are a bit more complex and you don't mind creating your own mappings.

Answer 9

My latest project uses the ASP.NET MVC framework and LINQ-to-SQL. For that reason, I depend heavily on the Repository pattern for my data layer. Since this is my first LINQ-to-SQL project, the database schema is represented within a single DataContext class. As later projects come along, I can splinter out common DataContexts for reuse.

I create an interface that holds all the signatures I plan to implement for a particular repository, which typically represents a single complex object. Based on that interface, I implement two classes: one for production, one for testing. All my LINQ-to-SQL queries are held in repository classes. In my controller code, I access the repositories for my data access methods. I do not include queries in my views; I use custom view model classes which get populated in the controllers using repository methods.

Answer 10

I've used Linq2SQL in a DAL DLL. I like the seperation of concerns plus any other sites or apps need to access the same database you can reuse quite of bit of code from your DLL. So all the Linq2SQL is stored away in this DAL. Now for Linq itself I use that in both my service and presentation layer depending on the need.

Linq2SQL DLL layout

Creating the mapping .dbml file to map SQL table.columns to a Linq Catalog (SqlRepository)

Wrote sql repository classes that match up to a Service layer class and use the proper Linq Catalog class.

Added interfaces for each sql repository class so the service layer works directly with the interface

Wrote business models that the sql repository classes pass back and forth with the service layer.