• First experience with LINQ

    Published by on January 21st, 2008 2:22 pm under .NET Framework 3.5, LINQ, Visual Studio 2008

    No Comments

    I’m finishing my second week at Southworks and I’m so happy about it, I met awesome people, but above all things, nice and easy-going partners.

    During this time I’ve been reading so much about Software Development and new technologies shipped with Visual Studio 2008, like LINQ & MVC Framework.

    I decided to write my first blog post about LINQ, my experiences & thoughts. I’ve been reading about it for some weeks, but I have just had the opportunity to try it.

    “LINQ to SQL” is an ORM (Object – Relational Mapping) that comes with VS 2008, it maps a relational database to .NET classes, so you can easily query and perform DML operations on the database just by using the generated classes which represents the DB objects. LINQ automatically generates the SQL and persists the changes you made.

    Visual Studio 2008 ships with a LINQ to SQL Designer, which greatly simplifies the task of modeling your DB, you can just drag and drop the DB objects you want from the Server Explorer to the designer, and they will be mapped including relationships among them, which are represented by arrows. You can obviously make custom changes to the model if you want.

    LINQ1

    Fig. 1 – LINQ to SQL Designer – A simple Northwind model

    DataContext Class

    Once you save your model, VS will generate .NET classes representing the entities and relationships in it, and also a custom DataContext Class will be generated.

    This class will provide us the interaction between the model and the real DB objects. It will have properties representing every modeled table, and also methods will be created for every SP added to the designer.

    Knowing this, we can see some query examples.

    Examples (C# Syntax)

    •  Select

    LINQ2

    Fig. 2 – Selecting products in "Condiments" category

    You can notice a few things from this code:

    First to use LINQ generated classes we have to instantiate a new DataContext class.

    Second, as we are selecting several products we have to store the query result in a Generic Collection of Products, or we can simply store it in the new VS2008 “var” type which infers the correct type.

    Third, LINQ syntax is very similar to regular SQL, but also having OOP advantages.

    Fourth, we query about the category name being equal to “Condiments”, if you remember the model, this is stored in a Category table, and related to Products table by CategoryID, so using standard SQL, will require joining both tables to obtain the same result, but with LINQ this is done in a simple and natural way.

    • Insert

    LINQ3

    Fig. 3 – Inserting a new category and two new products within it

    This code is self-explanatory; you create objects and set the properties you want.

    Then, you have to add the products to the new category using the Add method, after that add the new category in the Categories table, using the InsertOnSubmit method passing the category object as a parameter.

    Finally you persist changes with DataContext’s method, SubmitChanges.

    • Delete

    LINQ4

    Fig. 4 – Deleting all products in a category

    To delete the products we’ve inserted before, we will first query the DB, and store the results into a variable, note we used the new var type, so the compiler will infer that result is a collection of Products, then we call the DeleteAllOnSubmit method passing result as a parameter. This method will put all products in result in a pending delete state. Finally we commit deletion, using SubmitChanges method.

    • Update

    LINQ5

    Fig. 5 – Updating a single product 

    Update is very intuitive. You can update a product, first by retrieving it from the database, and then modifying its properties, finally as the previous cases, you have to commit the changes with SubmitChanges();

    For product’s retrieval we used a method called Single, which retrieves one and only one product that meets the given conditions and fails if the query returns more than one.

    If you noticed we used a different syntax in this query, it is called Lambda expression, I’ll try to explain this a little more on a next post. But its objective is to simplify even further LINQ syntax.

    Note: The syntax used is the one of Visual Studio RTM version, some methods had other names in VS betas, and because of this the syntax in older post of external blogs may vary.

    Conclusion

    As you have seen, LINQ is a powerful technology that will greatly simplify your work with DB. You have ORM capabilities shipped with Visual Studio 2008. You design your DB model, which is easy, and then it is even easier to query and perform DML operations on the DB. LINQ’s generated SQL statements are very efficient, and you can see the generated SQL of a statement in debug mode.

    With LINQ you can build simple applications easily, saving you a lot of written code, but it is also useful for complex scenarios.

    I hope you liked LINQ and want to give it a try, so I recommend you to pay a visit to Scott Guthrie’s awesome blog for more information and tutorials.