LINQ to SQL, LINQ to Entities

Just read a fascinating thread on the MSDN forums:

Very inefficient SQL generation in EF?

(although one of the posters there complained about how the link to this thread is not permanent...)

In the new world of .NET data access, it is no longer acceptable to deal with DataTables in the Business Layer of your application. DataTable is meant to bridge between the world of datastore (read: database) and business entities. In former days, Visual Studio's offering in the realm of closing the gap between data records and business entities was the strongly-typed DataSet (ADO.NET, if you please). When you added an SQL Server database to your project (this is still true of VS 2008), the IDE immediately started a wizard to generate a .xsd file defining classes derived from DataTable (and its relatives, DataRow and so on) that would be strongly-typed to match your database's schema. In essence, this wizard was about halfway to mapping your data from the hierarchal-relational model to the object model. It was one step short of having your records translated to full-fledged objects.

In order to achieve the full translation, a new mapper was needed: one that would translate your hierarchal-relational model to business entities. The disadvantage of such a mapper would be the way it limits querying: if the object you are talking to is not a table or view, you can't query it.

With the advent of LINQ, the story changed. Microsoft developed LINQ to SQL to fill the gap between table and business entity, and with the capabilities of LINQ, you can write code to query collections of objects. It's nearly as easy to write code in LINQ as it is to query tables of records in regular SQL. (Sometimes it's actually quicker to code in LINQ, but complex hacker-style queries are still best in SQL.)

The people who make Visual Studio actually published TWO object-relational mappers with VS2008: LINQ to SQL and LINQ to Entities. LINQ to SQL is heavily tied to SQL Server; LINQ to Entities is part of the Entity Framework, which is supposed to support many data providers.

It seems that the Entity Framework is still in the experimental stage, not yet market ready. The people on the forum linked above were unimpressed with the way LINQ to Entities translates your query into SQL. LINQ to SQL, on the other hand, rates pretty well on the performance end with payoffs in flexibility.

The bad news is that LINQ to SQL is no longer under development; MS sees the Entity Framework as its next-generation ADO.NET. The good news is that the LINQ to SQL team has been integrated into the Entity Framework team, so there's hope that at least the SQL Server implementation of LINQ to Entities will be as good as LINQ to SQL.

This being the case, LINQ to SQL will stick around a while longer, at least until the Entity Framework team manages to port the good things (read: provider-specific optimizations) from LINQ to SQL into their SQL Server provider.

|DataDirectory| distress

The issue first came up in a project last semester, and again it haunts us:
when you place your .mdf file in one project (the DAL) and execute the application from another project (the UI) evil things happen. Namely, you get an exception:

System.Data.SqlClient.SqlException was unhandled by user code
Message="An attempt to attach an auto-named database for file blah blah blah\PL\myDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

As I say, this is evil. The file myDB.mdf is right where I put it; when I go into Settings for my DAL project and open the nifty dialog box for my connection string, the "Test Connection" button works just fine.

But just you take a closer look at that error. When I compile my application, the system starts looking for my DB in the directory that contains my PL, not my DAL. What happened?

When I open the nifty dialog box, the path for my DB looks like this:

blah blah blah\DAL\myDB.mdf

The actual value of the setting in my DAL\Settings.settings file looks like this:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

Ha ha! Foul play! I can spend the whole day setting the location of the DB in the nifty dialog box, but in the bitter end the Settings.settings file translates the path of the parent project to a variable called |DataDirectory|, and at compile time it replaces my DAL project's path with the startup (UI) project's path for the value of that variable.

What's the right workaround?

1. The wrongheaded way would be to elbow my way into the Settings.settings and set the path absolutely to the location of my DB. Wrongheaded, because if I move my project around (even on my own machine), I have to elbow back in and update the location.

2. The easy way out would be to use SQL Server instead of a local data file. Which is of course what we're doing on our project.

3. I thought to try to place the DB in the parent folder of the projects - ie, in the solution folder. But that induced Settings.settings to hard-code the location. Not much better than solution 1.

4. Finally I found what I was looking for! by jumping from here to here to (at last!)

https://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx

So I added the following line of code to a function that is executed before I try to access my data:

"AppDomain.CurrentDomain.SetData("DataDirectory", AppDomain.CurrentDomain.BaseDirectory.Replace("GUI", "DAL") );"

where GUI is the directory of my UI and DAL is the directory of my DAL and they are in the same parent (solution) directory.

But really DBs were meant to be on servers, and anyway 3 layers are 2 layers too many. So claims Microsoft.


Now considering how much trouble this has caused a good number of people since 2005, I think it's time for Microsoft to rethink its nifty features.

Dude, where's my data access?

בהעמקת הכרותנו עם שכבת הממשק משתמש, אפשר כבר לראות את הצל של שכבת הלוגיקה המתקרבת. הגיע הזמן לקצת תכנון.

הנושא של היום הוא השאלה איפה לשים את הקוד שמפיק את שאילתותנו מול מסד הנתונים. החלטנו בשלב מוקדם יותר (ראה להלן) להשתמש ב
Linq to SQL
לשכבת ה
DAL.

LINQ to SQL,
כשגוררים עליו טבלאות ממסד נתונים של
SQL Server,
אוטומטית קורא ומפיק את היחסים בין הטבלאות שהגדרת במסד הנתונים






כששומרים את הקובץ
Linq to SQL,
מופק אוטומטית מחלקה בשם
DataContext - הקשר-נתונים

ביחד עם כל המחלקות שצריך כדי להציג את הישויות שלך כאובייקטים, כולל יכולות שמירה וקריאה למסד הנתונים.

המחלקות שאנחנו תכלי'ס כתבנו בשכבת ה
DAL
מבצעות את הלוגיקה הכי בסיסית של
CRUD (create, retrieve, update, and delete)
על כל סוג של ישות בנפרד. אציין שככה לא צריך אף פעם לגזור מחרוזת
SQL.
ואם בנינו נכון את היחסים, אף פעם לא צריך לבנות שאילתת
Linq
שמשמתמש ב
join.
המחלקות שיצר לנו את מחלקת ההקשר-נתונים אמורים לעשות לנו את כל העבודה.

היופי בזה הוא בכך שאם יש לי ביד ישות "בנאדם" או אוסף שלהן, כל עוד שהוא מקושר למחלקת ההקשר-נתונים (ז"א, לא עבר תהליך סריליזציה - נושא לכניסה אחרת) אפשר "למשוך" על מספרי הטלפון של הבנאדם (עם זו פעם הראשונה למופע הזה, לפחות) ואז הוא יבצע שאילתא מול מסד הנתונים ויוציא את המידע הצריך.

והינה השאלה של היום: כמה מהמשיכה הזו נכון לעשות בשכבת הלוגיקה?

פרט זה, של מתי מתבצע השאילתא, זה לא ענין, אלא איזה קוד אחראי על ביצועו. לכן אם קוד מופק אוטומטית של הקשר-נתונים נמצא בשכבת ה
DAL
וקיימת שורה בשכבת הלוגיקה שגורמת לו להתבצע, האם שברנו אף חוק של תכנות ב3 שכבות? נדמה לי שלא. ואם נחליט בתאריך מאוחר יותר להשתמש בטכנולוגיה אחרת בשכבת ה
DAL,
נוכל לספק מחלקות שמחשפות אובייקטים מיוחסים כמו אלה של
Linq to SQL.
ושכבת הלוגיקה לא צריכה אף פעם לדאוג מתי נקרא המידע ממסד הנתונים.

-------------------------------------------------

As we dig deeper into the UI layer, the ghostlike form of the Business Logic layer (BL) is peering out at us from the future. A little planning is in order.

Today's issue is the question of where to place the code that generates our queries on the DB. We decided at an early stage (see below) to use LINQ to SQL for our Data Access layer (DAL).

LINQ to SQL, when you drag tables from an SQL Server database onto it, automatically reads and generates the existing relationships between your tables.








When you then save your LINQ to SQL class, called a DataContext, it autogenerates a code file with all the classes necessary to

1) represent your data entities in your program as business entities

2) persist your entities to the database




The classes we wrote for the DAL do the very barest CRUD logic (create, retrieve, update, and delete) on one entity type at a time. Note that with LINQ to SQL, we need not ever write a single SQL string. If we built the relationships correctly, furthermore, we need not ever write a single LINQ query with a "join" - because the DataContext should be doing that all for us.

The beauty of LINQ is that once I have a Person or collection of Persons, as long as it is still attached to the generating DataContext (that means it has never been serialized and deserialized... a topic for a different post), I can pull on Person.Phones and that will (the first time I call it for this instance) query the database for the phone objects related to the person at hand.

And so, the question: how much of this pulling can responsibly be done in the BL?

The detail of when the query is executed is not an issue, it's only a question of which code is reponsible for doing it. So if the autogenerated code for the DataContext is in the DAL, and a line in the BL causes it to fire and query the DB, have we violated any priciple of 3-layered structure? I don't think we have. And if we decide at some later date to use LINQ to Entities or some other mechanism in our DAL, we can provide classes that expose the related objects just as well as LINQ to SQL does. And the BL need never worry about when the data is read from the DB.