LINQ - Updating Database

Hi,

This blog post summarizes the overview of LINQ.

Below are the new features been introduced in Orcas

§ Automatic Properties

§ Object Initializers

§ Collection Initializers

§ Extesnion Methods

§ Lambda Expressions - p => expressions

§ Query Syntax

§ Anonymous Types

§ Var Keyword

These language features help in making query against relational data. This overall querying programming model is called as "LINQ" - which stands for .NET Language Integrated Query.

Updating Database:

§ Data Context class is the main conduit by which we query our database and apply updates/changes. For e.g. "NorthwindDataContext".

§ After making the changes, required is to call the "SubmitChanges()" method on our DataContext to apply the changes back to the database. This will cause LINQ to SQL to dynamically calculate and execute the appropriate SQL code to update the database.

Incremental Update:

private void UpdateProduct()

{

NorthwindDataContext db = new NorthwindDataContext();

MyProduct product = db.MyProducts.Single(p => p.ProductName == "Chai");

product.UnitPrice = 99;

product.UnitsInStock = 5;

db.SubmitChanges();

}

Note: If a Product's UnitPrice/UnitInStock are already 99 and 5 then calling SubmitChanges() would not cause any database update statements to execute. i.e If the property values weren't changed by the property assignments above, then the object would not be considered changed and LINQ to SQL would therefore not execute an update for that product back to the database.

Insert/Delelte Database:

§ Insert/Delete is accomplished by adding/removing data objects from the DataContext's table Collections, and by then calling the SubmitChanges() method.

§ LINQ to SQL will keep track of these additions/removals, and automatically execute the appropriate SQL INSERT or DELETE statements when SubmitChanges() is invoked.

Transactions

§ A transaction guarantee that a series of individual actions occur atomically - meaning either they all succeed or they all don't.

§ When you call SubmitChanges() on your DataContext, the Updates Will Always Be Wrapped In A Transaction. This means that your database will never be in an inconsistent state if you perform multiple changes - either all of the changes you've made on your DataContext are saved, or none of them are.

§ If no transaction is already in scope, the LINQ to SQL DataContext object will automatically start a database transaction to guard updates when you call SubmitChanges().

§ Alternatively, LINQ to SQL also enables you to explicitly define and use your own TransactionScope object (a feature introduced in .NET 2.0). This makes it easier to integrate LINQ to SQL code with existing data access code you already have.

§ It also means that you can enlist non-database resources into the transaction - for example: you could send off a MSMQ message, update the file-system etc - and scope all of these work items in the same transaction that you use to update your database with LINQ to SQL.

Validation and Business Logic

§ LINQ to SQL supports a variety of ways for developers to cleanly integrate this with their data models.

§ LINQ to SQL enables you to Add This Validation Logic Once - and then have it be honored regardless of where/how the data model you've created is used.

Schema Validation Support

§ The data model classes defined using the LINQ to SQL designer, are by default annotated with some validation rules inferred from the schema of the tables in the database.

§ The datatypes of the properties in the data model classes will match the datatypes of the database schema.

§ This means we will get compile errors if we attempt to assign a boolean to a decimal value, or if you attempt to implicitly convert numeric types incorrectly.

§ If a column in the database is marked as being nullable, then the corresponding property in the data model class will be a nullable type.

§ Columns not marked as nullable will automatically raise exceptions if you attempt to persist an instance with a null value.

§ LINQ to SQL will likewise ensure that identity/unique column values in the database are correctly honored.

§ We can obviously override these default schema driven validation.

§ LINQ to SQL also Automatically Handles Escaping Sql Values for you - so you don't need to worry about SQL injection attacks when using it.

Custom Property Validation Support

§ Schema driven datatype validation is useful as a first step, but usually isn't enough for real-world scenarios.

§ For example: "Phone" property which is defined in the database as an nvarchar. Then “555-555-555” and “ABC-DEF-GHI” are valid telephone numbers.

§ To handle such situations, solution is to add a custom property validation rule to the entity Data model class (.e.g. Customer).

public partial class Customer

{

partial void OnPhoneChanging(string value)

{

Regex phone = new Regex(@"^[2-9]\d{2}-\d{3}-\d{4}$");

if (!phone.IsMatch(value))

{

throw new Exception("Not a valid Phone"); // Will prevent the assignment from taking place

}

}

}

§ LINQ to SQL exposes a number of custom extensibility points in DataContext classes that you can use to add validation logic before and after things take place. Many of these extensibility points utilize a new language feature called "partial methods" as above defined.

Custom Entity Object Validation Support

§ Property level validation as used for validating individual properties on a data model class.

§ However sometimes need is to validate multiple property values on an Object against each other.

private void ChangeDates()

{

NorthwindDataContext db = new NorthwindDataContext();

Order order = new Order();

order.OrderDate = DateTime.Now;

order.RequiredDate = DateTime.Now.AddDays(-1); // Valid date, however it makes absolutely no sense for the required delivery date to be yesterday

db.Orders.Add(order);

db.SubmitChanges();

}

§ Solution - Add Custom Entity/Object Level validation rules to guard against mistakes like this from happening.

§ To do this add a partial class for our "Order" entity and implement the OnValidate() Partial Method.

§ This will be invoked prior to the entity's values being persisted into the database. Within this validation method we can then access and validate all of the data model class properties:

public partial class Order

{

partial void OnValidate(ChangeAction action)

{

if (RequiredDate <>

{

throw new Exception("Not a valid Date");

}

}

}

Note: There are below partial methods been supported by an entity class at Property And Object Level validation.

#region Extensibility Method Definitions

partial void OnLoaded();

partial void OnValidate(System.Data.Linq.ChangeAction action);

partial void OnCreated();

partial void OnOrderIDChanging(int value);

partial void OnOrderIDChanged();

partial void OnCustomerIDChanging(string value);

partial void OnCustomerIDChanged();

partial void OnEmployeeIDChanging(System.Nullable value);

partial void OnEmployeeIDChanged();

partial void OnOrderDateChanging(System.Nullable value);

partial void OnOrderDateChanged();

partial void OnRequiredDateChanging(System.Nullable value);

partial void OnRequiredDateChanged();

partial void OnShippedDateChanging(System.Nullable value);

partial void OnShippedDateChanged();

partial void OnShipViaChanging(System.Nullable value);

partial void OnShipViaChanged();

partial void OnFreightChanging(System.Nullable value);

partial void OnFreightChanged();

partial void OnShipNameChanging(string value);

partial void OnShipNameChanged();

partial void OnShipAddressChanging(string value);

partial void OnShipAddressChanged();

partial void OnShipCityChanging(string value);

partial void OnShipCityChanged();

partial void OnShipRegionChanging(string value);

partial void OnShipRegionChanged();

partial void OnShipPostalCodeChanging(string value);

partial void OnShipPostalCodeChanged();

partial void OnShipCountryChanging(string value);

partial void OnShipCountryChanged();

#endregion

Custom Entity Insert/Update/Delete Method Validation

§ Read from reference post

Advanced: Looking at the Entire Change List for the Transaction

§ Read from reference post

Handling Simultaneous Changes with Optimistic Concurrency

In simultaneous updata system - One approach is to just "let the last writer win" . This is usually considered a poor (and incorrect) application experience.

Another approach which LINQ to SQL supports is to use an Optimistic Concurrency Model - where LINQ to SQL will automatically detect if the original values in the database have been updated by someone else prior to the new values being persisted. LINQ to SQL can then provide below:

· Conflict List Of Changed Values to the developer and enable them to either Reconcile the differences or

· Provide the end-user of the application with UI to indicate what they want to-do.

Reference: http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx

Hope this helps.

Thanks & Regards,

Arun Manglick || Tech Lead