A Taste of LINQ to SQL

Over the part few months, I'm using LINQ to SQL in my .Net projects.. It's one of the most attactive features I've in .NET Framework 3.5 .. Al tough the full release of LINQ to SQL is scheduled with upcoming Visual Studio Orcas; but still Visual Studio 2008 has most of its features..

Those who dont know anything about LINQ; well LINQ stands for Language Integrated Query and as the names shows LINQ is Code based language designed for data manipulation in different data structures like Lists, Array, KeyValue Pairs etc..


What is LINQ to SQL?
LINQ to SQL is an Object Relational Mapping implementation that ships in the .NET Framework "Orcas" release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it.

LINQ to SQL fully supports transactions, views, and stored procedures. It also provides an easy way to integrate data validation and business logic rules into your data model.

(Taken from ScottGu's Blog)


Reasons I Choose Linq to SQL
Reason number one.. LINQ is Cool :) Actually there are so many reasons for going with LINQ to SQL..

• LINQ is quite easy to understand
• Ddata manipulation can be done without breaking your nerves
• SQL Injection threat is very low.. Almost negligible
• We can use one single language through out the project both for Database Quering and internal data structures
• and bla bla bla...

Let's Get Started
Im breaking it in following parts..

• Creating the project
• The LINQ to SQL Connector and Data Context
• Whats the difference in LINQ And SQL Queries?
• Simple Queries
• Some Data Manipulation

Creating the Project
Lets start by creating a project.. Im going to use C# winform application and keep it as simple as I can.. There won't be any code architecture of things like that.. Just nice clean small chunks of code..

The LINQ to SQL Connector and Data Context
After creating the project; we'll need to add LINQtoSQL data connector to our project.. This will be used to include our database tables in our application.. Right click on your project then goto Add - > New item and the window will appear.. See to LINQtoSQLClasses.. You will see something like picture below..

Rename the class and click okay.. You will see a ____.dbml file in your project. Open it up and you'll see a window.. Click on the server explorer and a left side bar will appear with different data connections that you have created.. If you don't have any data connection create one by right clicking on dataconnection - > Addconnection.. See the image below

Now open up the database connection and expand Tables.. Now you'll see all the tables of your database in left side bar. Drag and Drop the tables in ___.dbml file.

If you have created Hard Relationships between tables; I would recommend to remove them as LINQ is quite handy with query based relationships.


Creating Data Context Connection
Now all we need to do is pass our connection string to data context class. I'd prefer using Properties.Settings in storing connection string but its up to you that you can also pass it directly..

 public ChatDataContext GetContext()  
     {  
       string ConnString = Properties.Settings.Default.UnisterDatabaseConnectionString.ToString();  
       //ChatContext udc = new UnisterDataContext("Data Source=MSSQLSERVER2008;Initial Catalog=UnisterDatabase;Integrated Security=True");  
       ChatDataContext udc = new UnisterDataContext(ConnString);  
       return udc;  
     }  

And that's it.. All we now have to do is call our data context method whenever we want to run a LINQ Query..


Whats the difference in LINQ And SQL Queries?
Im going to give you some examples which will hopefully show you differences between LINQ and SQL Queries..

• The Simple Select Query
  SQL Query : Select * from [ChatTest].[dbo].[Accounts] as at
  LINQ Query : from at in dc.Accounts select at
  
  You will clearly notice the changes of placing in keywords. In LINQ Query ALIAS is mandatory while in SQL Query its not.. Also a LINQ query should be start from keyword FROM and ends with the data we want to fetch, while in SQL we give data fetching fields in beginning.
  
  
• Select Query With Join & WHERE Clause
  
  SQL Query : Select cm.* from [ChatTest].[dbo].[ChatMessage] as cm INNER JOIN [ChatTest].[dbo].[Accounts] as a ON a.AccountID = cm.AccountID WHERE a.FirstName = 'kotwal'
  
  LINQ Query : from cm in dc.ChatMessage JOIN a in dc.Account ON cm.AccountID = a.AccountID WHERE a.FirstName == 'kotwal' SELECT cm
  
  Again you'll see there are no such differences but change of palacement in keywords (see colored letters) EXCEPT that LINQ query is far more simplified and readable than conventional SQL query.
  
  

Simple Queries

• The Insert and Update Query
  LINQ can perform both insert/update operations in one single query.. Here is a piece of code i want to share with you that I always use for insert/update operations.
  
  

    public void SaveAccount(Account Account)  
       {  
         using (ChatDataContext dc = conn.GetContext())  
         {  
           if (OBJECT.PKID > 0)  
           {  
             dc.OBJECT.Attach(OBJECT, true);  
           }  
           else  
           {  
             dc.OBJECT.InsertOnSubmit(OBJECT);  
           }  
           dc.SubmitChanges();  
         }  
       }  
  

  
  The object is passed in dataconext class.. If the object has PK then just attach the changes with previous data else insert that record on submitting chnages of datacontext.
  
  
• The Select Query
  I have already gave you some idea about select queries above.. Here I'm only going to share some more code with you..
  
  

         List<Account> result = null;  
         using (ChatDataContext dc = conn.GetContext())  
         {  
           IEnumerable<Account> accounts = (from a in dc.Accounts  
                            join m in dc.RoomMembers on a.AccountID equals m.AccountID  
                            where m.RoomID == RoomID && m.IsApproved==false  
                            select a);  
           result = accounts.ToList();  
         }  
         return result;  
  

  
  You can see in the code that I have used IEnumerable for resultant data.. I could've also used var but IEnumerable Exposes the enumerator, which supports a simple iteration over a non-generic collection. (Source: MSDN)
  
  
• The Delete Query
  The delete query is same is Insert/Update query with a minor difference.
  
  

    using (UnisterDataContext dc = conn.GetContext())  
         {  
           dc.Accounts.DeleteOnSubmit(OBJECT);  
           dc.SubmitChanges();  
         }  
  

  
  DeleteOnSumbit will sumbit the OBJECT data from database table.
  

Some Data Manipulation
As this post is for beginners; im only going to discuss some basic data manipulation using LINQ.. If you want to know more about it; look at MSDN articles.. There are few great articles on Code Project regarding LINQ...

• FirstOrDefault()
  Returns the first element of a sequence, or a default value if the sequence contains no elements. I would recommend this to use if LINQ queries otherwise you'll get an error in case there is no data to be retrieved.
  
  

   account = (from a in dc.Accounts where a.Email == Email select a).FirstOrDefault();
  

  
  
• Take()
  Returns a specified number of contiguous elements from the start of a sequence.
  
  

    IEnumerable blogs = (from b in dc.Blogs
                                             where b.IsPublished==true
                                             orderby b.UpdateDate descending
                                             select b).Take(6);
  

  
  
• Skip()
  LINQ to SQL translates Skip by using a subquery with the SQL NOT EXISTS clause
  
  

    IEnumerable blogs = (from b in dc.Blogs
                                             where b.IsPublished==true
                                             orderby b.UpdateDate descending
                                             select b).Skip(6);
  

  
  
• Count()
  Counts the number of elements; It's "SELECT COUNT" substitute of LINQ.
  
  

  Int32 blogs_count = (from bl in dc.Blogs where bl.AccountID == AccountID select bl.BlogID).Count();
  
  

  
  
• Distinct()
  Returns distinct elements from a sequence. The resultant sequence is unordered.
  
  

   var connections2 = (from cons in dc.Connections
                                                           where cons.AccountID != AccountID && cons.MyConnectionAccountID == AccountID
                                                           select new
                                                               {
                                                                   ConnectionID = cons.ConnectionID,
                                                                   AccountID = cons.MyConnectionAccountID,
                                                                   MyConnectionAccountID = cons.AccountID,
                                                                   CreateDate = cons.CreateDate,
                                                                   TimeStamp = cons.TimeStamp
                                                               }).Distinct();
  

  
  

In this post I've given you some ideas about working on LINQ.. If you want to learn more on LINQ; I'd suggest reading articles on Code Project.. I'll be back with more of LINQ series of arctiles soon...

Jazak Allah..