-
Do you know why you are using LINQ?
- using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Customers WHERE CompanyName LIKE '" + companyNameTextbox.Text + "%'";
bindingSource1.DataSource = cmd.ExecuteReader();
}
-
Figure: Bad example - using ADO.NET and not strongly typed
- var results =
from c in dbContext.Customers
where c.CompanyName.StartsWith(companyNameTextbox.Text)
select c;
customersBindingSource.DataSource = results;
or even
var results =
from c in dbContext.Customers
where c.CompanyName.StartsWith(companyNameTextbox.Text)
select new {c.CompanyName, c.Phone};
customersBindingSource.DataSource = results;
-
Figure: Good examples - at least 4 good reasons
- More readable and less code
- Less performance issues - Most serious .NET performance issues were because of unclosed connections. LINQ means no connection code needed to be done.
LINQ is another layer and really is overhead.
- Strongly typed fields - SQL tables/entities has intellisense
- Strongly typed SQL - SQL (Familiar SQL like syntax aka LINQ) has intellisense
-
Do you understand what "LINQ to Entities" has going for it?
LINQ to Entities is a more refined ORM than LINQ to SQL. It supports:
- Many to Many relationships
- In LINQ to SQL when you model a many to many relationship (e.g. Employees and Territories from Northwind) it involved 3 tables: Employee, EmployeeTerritories and Territory
- In LINQ to Entities it only uses the Employee and Territory tables and does not need the associative table EmployeeTerrirories
- Richer support for Inheritance
- Table per Hierarchy - store all the data from the inherited types into the same table e.g. Employee, Customer classes will store data into the Person table
- Table per Type - each type is stored in a separate tables
- Validation
- This means you can use the designer to make changes and validate it against your schema
- Update model from database
- This tells you if schema has changed and allows you to update the mapping from the designer. This was a short coming in LINQ to SQL
- Mapping Editor
- Allows you to use the designer to create the mappings - similar to the Java Hibernate editor (eclipse add-in)
-
-
Figure: Entity's mapping details
-
Do you understand what "LINQ to SQL" has going for it?
- Performance
- In a recent benchmark LINQ to SQL performed 5 times faster than LINQ to Entities
- Automatically unpluralizes table names when it generates classes
-
-
Figure: Bad example - LINQ to Entities takes the name straight from the Database
-
-
Figure: Good example - LINQ to Entities automatically unpluralizes the entities
-
Stored procedures are easier to work with
-
Relationship editor UI is nicer (more like SQL Server)
-
-
Figure: This popup form is way nicer
-
Do you know with LINQ to Entities you need to rename Generated Class
Names (aka Entity Type)?
LINQ to Entities does not unpluralize the table names when it generates classes. Thus, if your tables are named with purals (e.g. Customers instead of Customer table) then your Entities will also be pluralized. This makes your code confusing to read.
-
-
Figure: You have to rename or you will be confused
e.g. This is confusing
- NorthwindModel.NorthwindEntities db = new NorthwindModel.NorthwindEntities();
NorthwindModel.Customers customer = db.Customers.First();
-
Figure: Confusing code because you are saying "Customers" even though you are dealing with one customer
Note: That is why we don't use plurals in database tables - see rule "Object name should follow your company Naming Conventions" or use SSW SQL Auditor.
-
Do you know why you should choose "LINQ to Entities" over "LINQ to
SQL"?
This is a tough one to say... because at SSW we have used LINQ to SQL on many projects and have really enjoyed it (despite its a few short comings).
However the choice for the future is "LINQ to Entities".
These are the reasons:
- All of the posts that come on ADO.NET blog (blogs.msdn.com/ADONET) are giving entity love
- No notable new features in .NET 3.5 SP1 (the team only added support for SQL 2008 data types etc)
- It is still only for SQL Server?we need more eg. SQL Compact support
- LINQ to SQL was owned by the C# team, but is now owned by ADO.NET team. I expect they are now investing time and resources into ADO.NET Data Services and Entity Framework
- No mention of LINQ to SQL in preparation guide for upcoming .NET 3.5 certification, but there is a whole chapter on post 3.5 RTM Entity Framework
- Limited support for LINQ to SQL in ADO.NET Data Services (Only Read, no Insert or Update). In general, ADO.NET Data Services' primary support for ADO.Net data providers is via the Entity Framework
So it is likely LINQ to SQL will join the illustrious group of failed data programmability attempts (Object Spaces, WinFS)
PS: Stay tuned for this to be confirmed by the ADO.NET team. I expect we will eventually hear them say that "LINQ to SQL" will be a discontinued technology. I really am only mentioning this to save projects that are starting now and expecting this to be a framework to be improved in the future.
PPS: I also recommend you check out the great new post by Roger Jennings who confirmed our thoughts http://oakleafblog.blogspot.com/2008/05/is-adonet-team-abandoning-linq-to-sql.html
PPPS: Dam shame that excellent frameworks like CSLA are not being nailed by Microsoft.
-
Do you put the edmx and dbml in the right Folders (with the right
namespace)?
Namespace
- Edmx = Northwind.Data.Entity
- Dbml = Northwind.Data.SQL
-
-
Figure: Change the Namespace via the Property window
-
Do you understand the reason for the new connection string in the
Entity Framework?
-
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="NorthwindEntities"
connectionString=
"metadata=.\Northwind.csdl|.\Northwind.ssdl|.\Northwind.msl;
provider=System.Data.SqlClient;
provider connection string="
Data Source=(local);
Initial Catalog=Northwind;
Integrated Security=True;
MultipleActiveResultSets=True""
providerName="System.Data.EntityClient" />
<add name="WindowsUI.Properties.Settings.NorthwindConnectionString"
connectionString="Data Source=(local);Initial
Catalog=Northwind;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
-
Figure: Entity Connection String
The yellow is a new key called "XXXEntities" is used by the framework to construct the Entities at run time. It needs a reference in the metadata section to the *.csdl, *.ssdl and *.msl files which controls the mappings.
-
Are you aware of the generated .csdl, .ssdl and .msl files (or Do
you embed your metadata files to make it simpler)?
A lot of errors when trying to get LINQ to Entities up and running. A lot of them are caused by the locations of the *.csdl, *.ssdl and *.msl files. You can stuff around with relative paths and generated files or you can make sure they are embedded. You can do that by:
- You can stuff around with relative paths and generated files or...
Step 1: Change the EDMX's Metadata Artifact Processing property to "Embed in Output Assembly"
-
Step 2: copy the connection string in app.config from the data to the UI
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="NorthwindEntities"
connectionString="metadata=.\Properties\DataSources\Northwind.csdl|.\Properties\DataSources\Northwind.ssdl|.\Properties\DataSources\Northwind.msl;
provider=System.Data.SqlClient;provider connection string="Data Source=(local);Initial
Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True""
providerName="System.Data.EntityClient" />
</connectionStrings>
</configuration>
-
Figure: Bad example - This is all relative from the .exe path and VS.NET does not reliably copy these files when you build
- <?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="NorthwindEntities"
connectionString="metadata=res://*/Model.Northwind.csdl|res://*/Model.Northwind.ssdl|res://*/Model.Northwind.msl;
provider=System.Data.SqlClient;provider connection string="Data Source=(local);Initial
Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True""
providerName="System.Data.EntityClient" />
</connectionStrings>
</configuration>
-
Figure: Good example - It is always there - but the problem is that it cannot be shared with another application
-
Do you use SQL metal for the generation for DBML?
-
-
Figure: Bad - don't use designer
- SqlMetal /server:.\sqlexpress /database:northwind /dbml:northwind.dbml
-
Figure: GOOD - it is always there - the problem is that it cannot be shared with another app
SqlMetal is the command line tool used by LINQ to SQL to generate entities. Developers
should use this instead of the DBML designer because it is easy to regenerate when
there is a schema change. See
Do you use code generators.
Here is a comparison of SqlMetal and the DBML designer.
|
Feature
|
DBML Designer
|
SqlMetal
|
| Ease of regeneration |
 |
 |
| Customizing classes easily |
|
|
| Specifying Inheritance |
|
|
| Generating Classes from SQL Server Compact |
|
|
| External Mapping File |
|
|
| Specifying a base class |
|
|
| Mark classes as serializable (for WCF/Web Services) |
|
|
Figure: Comparison of SqlMetal and the DBML designer
Note: If you are using LINQ to Entities, then you can use the designer because it can get schema changes.
-
Do you use SQL metal + powershell to keep your DBML clean?
[Details to come from Adam]
-
Do you understand the wizard adds references to System.Data.LINQ
or System.Data.Entitiy?
-
-
Figure: You will need to add these 2 references in your UI
Tip: copy and paste the reference (you will need the PowerCommands for Visual Studio 2008)
- using System.Data;
using System.Data.Entity;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Northwind.Data;
using Northwind.Data.Model;
-
Figure: You will need to add these 3
-
Do you understand what breaks when schema changes and how to fix it in LINQ to Entities?
If you add EmailAddress field to Customers table and change PostalCode to PostCode:
-
-
Figure: Add EmailAddress field to Customers table and change PostalCode to PostCode
Your build will succeed but if you run the application you will see a runtime error
-
-
Figure: Run and see if any errors
To update it you right click the Entity Model and Update Model from Database
-
-
Figure: Update model from database
-
Do you store object context in a member variable for Windows Forms?
Do you remember to be a good boy with connections
- private void searchButton_Click(object sender, EventArgs e)
{
Northwind.Data.Model.NorthwindEntities db = new
Northwind.Data.Model.NorthwindEntities();
var results =
from c in db.Customers
where c.CompanyName.StartsWith(companyNameTextbox.Text)
&& c.Country == "UK"
select c;
customersBindingSource.DataSource = results;
}
-
Figure: Bad - creating Object Context each time is expensive + you can't save it
- Using(Northwind.Data.Model.NorthwindEntities db = new
Northwind.Data.Model.NorthwindEntities())
{
var results =
from c in db.Customers
where c.CompanyName.StartsWith(companyNameTextbox.Text)
&& c.Country == "UK"
select c;
customersBindingSource.DataSource = results;
}
-
Figure: Bad Too - using does not detach from the result - so we are not allowed to save changes, in another context
- public partial class CustomerForm : Form
{
//member variable to keep track of changes
private Northwind.Data.Model.NorthwindEntities dbContext =
new Northwind.Data.Model.NorthwindEntities();
public CustomerForm()
{
InitializeComponent();
//Add handler for cleanup
this.Disposed += new EventHandler(CustomerForm_Disposed);
}
void CustomerForm_Disposed(object sender, EventArgs e)
{
dbContext.Dispose();
}
}
-
Figure: Good - create a member Object Context for the form (so we can save later)
-
Do you use "var"?
Despite what it looks like, the var keyword is not a throwback to the dark ages where we did not have strongly typed variables. It is just a short hand to save developers from typing out the type of a variable.
-
-
Figure: var is strongly typed
- IQueryable<Customers> results =
from c in dbContext.Customers
where c.CompanyName.StartsWith(companyNameTextbox.Text)
select c;
customersBindingSource.DataSource = results;
-
Figure: Bad - you should just use "var" instead of "IQueryable"
- var results =
from c in dbContext.Customers
where c.CompanyName.StartsWith(companyNameTextbox.Text)
select c;
customersBindingSource.DataSource = results;
-
Figure: Good - using "var" to save few keystrokes
-
Do you use LINQ syntax for search forms instead of stored procs?
-
-
Figure: Use LINQ syntax for search forms
- private void SearchButton_Click(object sender, EventArgs e)
{
NorthwindDataContext db = new NorthwindDataContext();
dataGridView1.DataSource = db.Ten_Most_Expensive_Products();
}
-
Figure: bad example - stored proc
- private void SearchButton_Click(object sender, EventArgs e)
{
var customers = from c in db.Customers
select new { c.ContactName, c.Phone, c.City, c.PostalCode };
SearchResultsGrid.DataSource = customers.Where(cust =>
cust.ContactName.StartsWith(SearchTextBox.Text));
}
-
Figure: good example - LINQ
Note: We recommend this approach because it's a lot easier to add filters to your LINQ query then it is to update your stored procedure.
-
Do you use Stored Procs only for performance issues?
Not just saving a round trip.
Only do this if you have a performance issue AND you have measured it, see Do you implement business logic in middle tier?
-
Do you use DBML only for stored procs?
-
-
Figure: DataGridView - Use DMBL only for stored procs
- private void TenMostPopularForm2_Load(object sender, EventArgs e)
{
//instantiate context
//exec proc
//EDMX = Entities
//DBML = DataContext
//NorthwindEntities db = new NorthwindEntities();
//dataGridView1.DataSource = db.CreateQuery<
Order_Details_Extended>;
NorthwindDataContext db = new NorthwindDataContext();
dataGridView1.DataSource = db.Ten_Most_Expensive_Products();
}
-
Figure: Code - Use DMBL only for stored procs
Note: This may change
Note: It is bad to use DBML because it cannot return strongly typed business objects from the entity framework - DBML returns it own generated class
-
-
Figure: DBML Stored Procedure
Instead of
-
-
Figure: DBML returns it own generated class
-
Do you always use the Smart Tasks for binding?
It automatically updates now because it is reading from the LINQ object.
- Click the Smart Tasks on the DataGridView
- Click Add Project Data Source
-
-
Figure: Use Smart Tasks for binding
-
Do you know why this Microsoft Entity Framework is so exciting?
-
-
Figure: MS Vision of EDM in their world
-
Do you know how to use LINQ in a 3 tiered architecture?
By default when you generate a LINQ DBML file the business objects and the DataContext are bundled into the DBML file. This restricts LINQ-to-SQL to being a 2 tiered architecture like so:
-
-
Figure: Bad Example - 2 tiered architecture as the UI has direct access to the DataAccess layer
-
-
Figure: Good Example, UI only has access to the business objects and business layer. This is a 3 tiered architecture.
To achieve this you need to:
-
Create a Common project with the DBML file
-
Make the generated DataContext internal
-
Create a DataAccess project and add a DataContext class with the following methods:
-
public class NorthwindData : IDisposable
{
/// <summary>
/// Private instance of a generic DataContext
/// </summary>
private DataContext _innerDataContext = null;
/// <summary>
/// Gets an instance of the generic DataContext
/// </summary>
protected DataContext DataContext
{
get
{
// If it doesn't exist yet then instanciate it
if (_innerDataContext == null)
{
_innerDataContext =
new DataContext(
ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
}
return _innerDataContext;
}
}
public void Dispose()
{
_innerDataContext.Dispose();
}
/// <summary>
/// Gets a table from the generic data context
/// </summary>
/// <typeparam name="T">Type T of the Table</typeparam>
/// <returns>A table of T</returns>
public Table<T> GetTable<T>() where T:class
{
return DataContext.GetTable<T>();
}
/// <summary>
/// Update an entity in the database
/// </summary>
/// <typeparam name="T">Type of the entity</typeparam>
/// <param name="entity">Entity to update</param>
public void Update<T>(T entity) where T:class
{
DataContext.GetTable<T>().Attach(entity, true);
DataContext.Refresh(RefreshMode.KeepCurrentValues, entity);
}
/// <summary>
/// Submits the changes back to the database
/// </summary>
public void Save()
{
DataContext.SubmitChanges();
}
}
-
This layer will be used by your business layer
-
Your UI will reference the business layer
-
WCF Checklist - Do you mark your DBML as serializable?
By default any class that needs to be passed across WCF needs to be serializable. When you generate a DBML file it doesn't not mark any of the entities as serializable. You will set the serialization mode to Unidirectional otherwise you might encounter an error like this:
-
-
Figure: The exception.
-
-
Figure: Set the "Serialization Mode" to "Unidirectional".
Note: you can also set this up in SQL Metal with the parameter /serialization:unidirectional
-
WCF Checklist - Do you have a timestamp column on all your tables?
When doing updates across WCF, LINQ-to-SQL works in disconnected mode where your entities are detached from the original data context that retrieved it. As such all change tracking is lost so updates are difficult.
To avoid issue make sure you add a timestamp columns to all your tables.
-
-
Figure: Add the TimeStamp in table.
This is used for concurrency control.
Note: We have a tool called SSW SQL Auditor (link here) that checks for this
-
WCF Checklist - Do you use Attach and Refresh to do updates?
Updates have always been tricky in disconnected mode. The best method that we have found is to use the Attach as modified method with a Refresh of the context.
-
public void UpdateCustomer(Customer customer)
{
DataContext.Customers.Attach(customer, true);
DataContext.Refresh(RefreshMode.KeepCurrentValues, customer);
}
-
Figure: Updates have always been tricky in disconnected mode
-
WCF Checklist - Do you always retrieve data in small chunks?
By default WCF limits your messages to 64KB. That means that any queries returning SELECT * FROM Customers will fail with an error like:
-
-
Figure: Exception of use "Select * from..."
You can fix this issue by:
-
Selecting data by paging (recommended)
-
Changing the MaxReceivedMessageSize property in the Web.config
Note: This will only be used if you absolutely must return something greater than 64KB (e.g. an Image or other Binary data)
-
public IList<Customer> GetCustomers()
{
using (var db = new NorthwindDataContext())
{
return db.Customers.ToList();
}
}
-
Figure: Bad Example - This is exactly the same as doing a SELECT * FROM Customers which is a definite no.
-
public IList<Customer> GetCustomers(int pageIndex, int pageSize, out int total)
{
using (var db = new NorthwindDataContext())
{
var customers = db.Customers;
total = customers.Count();
var results = customers
.Skip(pageIndex * pageSize)
.Take(pageSize);
return results.ToList();
}
}
-
Figure: Good Example - This will allow the service to return a discrete chunk of data instead of the whole table. It is more efficient this way
-
Do you know we should name the EDMX and DBML the same as Database?
-
-
Figure: Good Example - These names are consistent with the Database's name
-
-
Figure: Bad Example - These names are inconsistent with the Database's name