SA Developer .NET

I am busy with my first application that uses LINQ and the architect has decided that we will be using LINQ to SQL for data access.

I was very excited until I realised that there is no possible way to implement a 3 layer / tier application design model because your presentation layer needs to reference your data access layer to get the entity types from the data context.

I would like to know if anyone has played with this and managed to overcome this shortfall... All the searches I did on google returned lots of gripes about this exact problem but no solutions 

---

SA Developer .Net Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.

No idea for a fix, just be aware that LINQ has a similar effect on SQL Server's procedure cache as ad-hoc SQL does, especially if you're using a lot of varchar/numeric parameters. I don't suppose the architect is considering using stored procs with LINQ?

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

GilaMonster:

No idea for a fix, just be aware that LINQ has a similar effect on SQL Server's procedure cache as ad-hoc SQL does, especially if you're using a lot of varchar/numeric parameters. I don't suppose the architect is considering using stored procs with LINQ?

We are going to use stored procedures as the LINQ methods for certain actions. I have already brought up the issue of security and access to the base tables so hopefully the stored procs will assist us.

What really sucks is that if you want to use projection in your LINQ queries you have to return an anonymous type which I can already see is going to cause some issues if I need to use attributes for that type.

I must say I am not impressed so far! Will keep updating this thread to assist others with their decision making when it comes to this new technology.

---

SA Developer .Net Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.

Haven't read through it attentively, but maybe this can point you in a direction:

http://codebetter.com/blogs/ian_cooper/archive/2007/12/02/architecting-linq-to-sql-applications-part-3.aspx

cheers

I agree with the statement that Linq to SQL is a RAD tool, I don't believe it holds the features required for a full 3-tier design.

For small applications though it can be made to work as a powerful quick business layer + data access mush.

We implemented something similar for one of our projects where we used CSLA's method of validation and business rule application and combined it with the Linq-to-SQL entities a la this. The result was a quick data access layer with some business logic added on.  It took the guys a bit of time to come round to the fact that the entities didn't actually fall within the data layer but more the business layer, but eventually it took.

As to the performance problems: like I said, I see Linq-to-SQL falling in to the "i need a small business layer quick" category, not the "i need a robust abstraction of my business logic."  If it's a quick thing for a small system, I'm going to assume that having a proccache thats a little unwiedly is either bearable or you're using Linq to SQL as a stepping stone to a better solution.

---

Why go against tradition when we can admit defeat, live in decline, be the victim of our own design?
http://dotnet.org.za/calmyourself

CalmYourself:

I agree with the statement that Linq to SQL is a RAD tool, I don't believe it holds the features required for a full 3-tier design.

For small applications though it can be made to work as a powerful quick business layer + data access mush.

We implemented something similar for one of our projects where we used CSLA's method of validation and business rule application and combined it with the Linq-to-SQL entities a la this. The result was a quick data access layer with some business logic added on.  It took the guys a bit of time to come round to the fact that the entities didn't actually fall within the data layer but more the business layer, but eventually it took.

As to the performance problems: like I said, I see Linq-to-SQL falling in to the "i need a small business layer quick" category, not the "i need a robust abstraction of my business logic."  If it's a quick thing for a small system, I'm going to assume that having a proccache thats a little unwiedly is either bearable or you're using Linq to SQL as a stepping stone to a better solution.

Excellent summary!

---

SA Developer .Net Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.

---

The only source of knowledge is experience.

pat.ramadass:

This... 

 

N-Tier and Remote Applications with LINQ to SQL

http://msdn.microsoft.com/en-us/library/bb882661.aspx

 

...may help? Using an ORM such as LLBLGen Pro with its LINQ to LLBLGen Pro provider may be a good option? We use it.

 

Thanks,

 

-Pat Ramadass

Don't think we will be able to move away from the SQL provider for LINQ but that link you sent me was very useful.

Through the use of partial classes your Business object and DAL become one layer and the use of a proxy that returns XML representations of the entities will create a layered / n-tier architecture which will prevent the presentation layer from knowing anything about the data access layer which is what I want to achieve.

---

SA Developer .Net Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.

Agree that this is a problem. I think it helps to consider LinqToSql a data mapper (like IBatis, without the xml), from which you can quickly and easily generate type safe queries from a database. I thus have my own shapes defined in a domain library, and map these from LinqToSql (you can do the mapping inline with the LINQ, which allows you to take full advantage of deferred execution and other IQueryable goodness). Shout if I need to elaborate more on this.

Even though some might consider this duplication, it's a lot quicker and better than rolling your own. You also get a layer away from your database, which can be appropriate if your domain doesn't look like a relational store. When updating, though, you need to tranverse object graphs yourself, but if the alternative is rolling your own you're probably still better off.

My dislikes of it are having to extend partial classes (and partial methods) for customisation, lack of acceptable many to many support, and the database first approach (especially without effective updating - changing your schema hurts at times). These are all manageable, though, just depends on the project. (I currently use LinqToSql in a small side project or two, but generally use NHibernate.)

Also re the projection, can't you map the anon type to something typed? Not sure what you need the attibutes for, but that's still an option.

GilaMonster, can you please elaborate on the procedure cache, or point me to a reference?

---

The high road might seem longer at first, but you'll enjoy the downhill at the end...

http://blog.benhartonline.com/

benhart:

GilaMonster, can you please elaborate on the procedure cache, or point me to a reference?

Microsoft Connect.  It was originally reported by Adam Machanic  It's very easy to duplicate the problem.  Basically it means that:

from f in foo where name == "asd"

generates a different query to

from f in foo where name == "asdf"

 as the first one has @p0 NVARCHAR(3) and the second has @p0 NVARCHAR(4)

---

Why go against tradition when we can admit defeat, live in decline, be the victim of our own design?
http://dotnet.org.za/calmyourself

Precisely. It gets worse when you toss numerics in too.

from f in foo where value == 5.2
from f in foo where value == 5.26
from f in foo where value == 25.2
from f in foo where value == 25.26

all generate different entries in the proc cache. The first is a numeric (2,1), the second a numeric (3,2), the third a numeric (3,1) and the last a numeric (4,2)

Multiple similar entries in the procedure cache mean more memory usage by SQL and less effective plan reuse. Less plan reuse results in more compiles and recompiles, driving the CPU higher.

It's one of those things that can vary from unnoticable to killing the server, depending on what else is happening, how much load the server and what kind of queries you're actually running from LINQ.

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

Crumbs. Thanks for the heads up - that's nasty. Hope they resolve that soon.

---

The high road might seem longer at first, but you'll enjoy the downhill at the end...

http://blog.benhartonline.com/

benhart:

Crumbs. Thanks for the heads up - that's nasty. Hope they resolve that soon.

Last I heard, the LINQ team were denying that it was a problem at all. I doubt it's unique to LINQ. I'll be very surprised if the other ORM tools don't have similar issues, just not as well known

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

GilaMonster:

I doubt it's unique to LINQ. I'll be very surprised if the other ORM tools don't have similar issues, just not as well known

<brag>

Our roll-your-own ORM and code-gen tools work beautifully with SQL.  All data access is done through stored procs, parameters are sized correctly, naming conventions can be enforced, procs are on seperate schemas to tables, business rules and validations can be applied either in code, or SQL, or both...

</brag>

---

Why go against tradition when we can admit defeat, live in decline, be the victim of our own design?
http://dotnet.org.za/calmyourself

CalmYourself:

All data access is done through stored procs,

That's what most ORMs don't do.

The comments I see often are that Stored Procs are out of date, belong last century, are so slow to develop that they hinder RAD and that in the fast-paced development world of today automatically generated SQL is the 'proper' way to do things.

After all, programmers shouldn't have to waste their time doing low-level database work. They've got far more important things to do than that.

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!