SSW Rules to Better LINQ

1. 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

   1. More readable and less code
   2. 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.
   3. Strongly typed fields - SQL tables/entities has intellisense
   4. Strongly typed SQL - SQL (Familiar SQL like syntax aka LINQ) has intellisense

2. Do you understand what "LINQ to Entities" has going for it?

   Many to Many - Not a 1 to 1 mapping... it removes Many to Many eg. EmployeeTerritories
   Concrete classes - Person.Type (Customer, Employee) Turn into 2 different classes
   Validate - means you can use the designer
   Update model from database - tells you if schema has changed - means you can use the designer
   Mapping Editor - means you can use the designer - similar to the Java Hibernate editor (eclipse add-in)

   

   Figure: Entity's mapping details

3. Do you understand what "LINQ to SQL" has going for it?

   Stored procedures
   Designer UI for relationships is nicer

   

   Figure: This popup form is way nicer

4. Do you know with LINQ to Entities you need to rename Generated Class Names (aka Entity Type)?

   

   Figure: You have to rename or you will be confused

   Eg.
   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.

5. 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.

6. Do you put the edmx and dbml in the right Folders (with the right namespace)?

   Namespace

   • Edmx = Northwind.Data.Model (Suggestion to Microsoft ?make it nullable, as per Dbml)
   • Dbml = nothing (so it will default to folder name eg. Northwind.Data.SQL)

   
   
   

   Figure: EDMX Suggestion to Microsoft ?make it nullable, as per Dbml

7. 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=&quot;
   Data Source=(local);
   Initial Catalog=Northwind;
   Integrated Security=True;
   MultipleActiveResultSets=True&quot;"
         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

8. Are you aware of the generated .csdl, .ssdl and .msl files (or Do you embed your metadata files to make it simpler)?

   You can stuff around with relative paths and generated files or...
   
   Step 1: Change the EDMX to embed
   

   

   
   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=&quot;Data Source=(local);Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True&quot;"
   providerName="System.Data.EntityClient" />
     </connectionStrings>
   </configuration>

   Figure: BAD ?this is all relative from the .exe path ?VS.NET does not reliably copy (or I don't know how it works)

   <?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=&quot;Data Source=(local);Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
     </connectionStrings>
   </configuration>

   Figure: GOOD ?it is always there ?the problem is that it cannot be shared with another app

9. 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: EDMX is fine to continue to use the designer because it can get schema changes.

10. Do you use SQL metal + powershell to keep your DBML clean?

    [Details to come from Adam]

11. 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

12. Do you understand what breaks when schema changes?

    Add EmailAddress field to Customers table and change PostalCode to PostCode

    
    

    Figure: Add EmailAddress field to Customers table and change PostalCode to PostCode

    Run and see if any errors

    
    

    Figure: Run and see if any errors

    Go to Entity Model

    
    

    Figure: Update model from database

13. 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)

14. Do you use "var"?

    It is not a variant... it save code and is strongly typed
    

    
    

    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 ?use "var"

15. 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: Reason is quicker to add more criteria (as no touching of the database)

16. 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?

17. 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

18. Do you always use the Smart Tasks for binding?

    It automatically updates now because it is reading from the LINQ object.
    

    1. Click the Smart Tasks on the DataGridView
    2. Click Add Project Data Source

    
    

    Figure: Use Smart Tasks for binding

19. Do you know why this Microsoft Entity Framework is so exciting?

    
    

    Figure: MS Vision of EDM in their world

Acknowledgements

Adam Cogan
Ryan Tee