Inner Join, Cross Join and Left Outer Join With LINQ to SQL

The join operations in this article are done using COURSE and STUDENT tables. So now I am going to explain the joins to be done using LINQ to SQL step-by-step.

Step 1: Create COURSE and STUDENT Tables in the database as in the following:
 
 CREATE TABLE COURSE
 (
     course_id int IDENTITY(1,1) PRIMARY KEY,
     course_name nvarchar(70) NOT NULL,
     course_desc nvarchar(255) NULL,
     modified_date date NULL,
 )
 CREATE TABLE STUDENT
 (
     student_id int IDENTITY(1,1) PRIMARY KEY,
     student_name nvarchar(70),
     student_city nvarchar(30),
     course_id int NOT NULL
 )
 
Step 2: Define foreign key constraints on the STUDENT table as in the following:
 
 ALTER TABLE STUDENT
 ADD CONSTRAINT [FK_STUDENT_COURSE] FOREIGN KEY (course_id)REFERENCES COURSE(course_id)
 
Step 3: Create Data Layer
 
Create a dbml file (Operation.dbml in this article) and using the Object Relation Designer create a data context class for the STUDENT and COUSRE tables.

In the above figure we drag and drop both the COURSE and STUDENT tables onto the designer map of the Operation.dbml file and that shows the relationship between the COURSE and STUDENT tables.
 
Step 4: Inner Join in LINQ to SQL
 
 OperationDataContext odDataContext = new OperationDataContext();
 var studentInfo = from student in odDataContext.STUDENTs
 join course in odDataContext.COURSEs
 on student.course_id equals       course.course_id
 select new { student.student_name, student.student_city, course.course_name, course.course_desc };
 
In the above code we join the STUDENT table and the COURSE table using the "join" keyword and using the "on" keyword join the tables by the course_id field of both tables. It returns all rows with a common course_id in both tables.
 
Step 5: Cross Join in LINQ to SQL
 
 OperationDataContext odDataContext = new OperationDataContext();
 var studentInfo = from student in odDataContext.STUDENTs
 from course in odDataContext.COURSEs
 select new { student.student_name, student.student_city, course.course_name, course.course_desc };
 
In the above code we are doing a cross-join on both the STUDENT table and the COURSE table. We get all rows from both tables and the total rows are STUDENT table rows * COURSE table rows.
 
Step 6: Left Join in LINQ to SQL
 
 OperationDataContext odDataContext = new OperationDataContext();
 var courseInfo = from course in odDataContext.COURSEs
 join student in odDataContext.STUDENTs
 on course.course_id equals  student.course_id into studentInfo
 from students in studentInfo.DefaultIfEmpty()
 select new 
 {
     STUDENTNAME = (students.student_name == null)? NULL":students.student_name, 
     STUDENTCITY = (students.student_city == null)? "NULL":students.student_city,
     COURSENAME = course.course_name, 
     COUSREDESCRIPTION = course.course_desc 
 };
 
In above code we are doing a Left Join. Here the left table is COURSE. So it will return all rows from the course table not depends STUDENT table course_id field. If the course_id field value is in the COURSE table but not in the STUDENT table then in the row course_name and course_desc fields will show and student_name and student_city fields will show NULL. Here the output will be the total number of rows in the COURSE table. 

I found one more helpfull link  Click here.

Continue Reading →

How Data Annotations Work for ASP.NET MVC Validation

Data validation is one of the most important aspects of developing applications for the web. However, validation is also something that can get messy pretty quickly, as developers often stick validation code anywhere and everywhere. However, if you keep a clear separation of concerns by using the MVC pattern and couple that with data annotations in the model, then your validation code becomes much simpler to write, maintain, and test. The added bonus: simple code that's well written & tested is code that's just going to work.

What are data annotations?
Data annotations are attribute classes that live in the System.ComponentModel.DataAnnotations namespace that you can use to apply to (decorate) classes or properties to enforce pre-defined validation rules. These annotations are available across various Visual Studio 2010 project types including ASP.NET MVC, Web Forms, ASP.NET Apps & Web Sites, Dynamic Data & even non ASP.NET projects like Silverlight & WPF. The same holds true for the data models; you can use these annotations with POCOs (plain old CLR objects), EF models, Linq2SQL models, etc... The bottom line is that you can use data annotations anywhere.

There are several out of the box data annotations to choose from:

Required
Regular Expression
Range
ZipCode
DisplayName
DisplayFormat
Scaffold
DataType
StringLength
A few more...

Although having this set of built in validations is great, it is a limited set. Since every business has lots of rules that don't neatly fit into this pre-defined set of attributes, what do you do when you need to use your own logic? It's quite easy, really - just derive from any of the inheritable (non sealed) attribute classes in the System.ComponentModel.DataAnnotations namespace, then code your business rules in the derived class methods.

Between the built-in validators and your own custom validation code, you're all set with maintainable and clean validation. However, server side data sanitization & validation is also equally important. An upcoming blog post will cover creating custom & server side validation. In the meantime, Brad Wilson from the ASP.NET team has some great blog posts on unobtrusive validation along with a a great series of posts on MVC.

And it all starts in the data model...

Applying data annotations to the model.
Suppose a data entry clerk needs to frequently update baked products with baked and expiration dates, amounts, prices, etc... To accomplish this in code, start with a basic POCO class to represent a product and will serve as the data model to meet the needs of this scenario. You'll need to do more than just create the class and its members though, and that's where annotations come into play.

Data annotations serve as a way to enforce these common validation scenarios without having to write much code, and more importantly, without writing repetitive code. Annotations live in one place - the model, rather than a code behind, web page, controller, or view. This helps prevent data validation code from turning into Spaghetti# code[1] scattered about your project.

Take a look at the following Product class, complete with a few basic data annotations:

public class Product 
{
public int Id { get; set; }

[DisplayName("Delicious Treat")]
[Required(ErrorMessage = "The product name field is required.")]
public string Name { get; set; }

[Required(ErrorMessage = "The product description field is required.")]
public string Description { get; set; }

[DisplayName("Sale Price")]
[Required(ErrorMessage = "The Sale Price field is required.")]
public decimal Price { get; set; }

[DisplayName("Freshly Baked on")]
[Required(ErrorMessage = "The Freshly Baked On field is required.")] 
public DateTime CreationDate { get; set; }

[DisplayName("Don't Sell After")]
[Required(ErrorMessage = "The Expiration Date field is required.")]
public DateTime ExpirationDate { get; set; }

[DisplayName("Qty Available")]
[Required(ErrorMessage = "The Qty Available field is required.")]
[Range(0,120)]
public int QtyOnHand { get; set; }

[DisplayName("Product Image")]
public string ImageName { get; set; }
}

The above properties are labeled with attributes that clearly state their intentions. The bulk of the validation is simply the Required attribute for this sample, along with a Range attribute on the QtyOnHand property. Some attributes such as the DisplayName attribute don't necessarily validate but do affect the rendered output. As the above sample demonstrates, any combination of necessary attributes can decorate properties. Notice that all the model and validation code so far live in the same place, and will continue to do so when you create custom attributes and validators; meaning that you'll continue to have a nice, clean separation of concerns in your app.

Once you're done applying attributes to the model, you can move on to the controllers and views.

Where do views & controllers fit in?
Views:

Once you've setup validation on the model using data annotations, they're automatically consumed by Html Helpers in views so the helpers can render the proper HTML output. For client side validation to work, you will need to ensure that these two <SCRIPT> tag references are in your view:


<script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script> 

 Note: If you're using ASP.NET MVC 3 project templates, these script tags will be added in views automatically for you by selecting the "reference client script libraries" option in the Add View dialog.

The following code from an edit or insert view will make sure the DisplayName, Required, and Range attributes work at run time.

<div class="editor-field">
@Html.EditorFor(model => model.QtyOnHand)
@Html.ValidationMessageFor(model => model.QtyOnHand)
</div> 

Other helper methods such as @Html.TextBox, @Html.TextBoxFor, @Html.Editor, etc... render nearly identical yet very slim output, explained more later in this post. Views play an integral part in validation, yet as the developer you often won't need to do much in the view other than use the built-in Html Helpers.

Controllers:
Controllers serve as a gateway to accept HTTP POST requests to process incoming data then to match that data with its model and view for rendering. Controllers shouldn't be a dumping ground for validation code, server side validation code should be called from controller action methods. While you can put validation code directly into the action methods themselves; the practice tends to lead to code that's less than optimal to maintain, since you've started scattering the code about the project. Rather than having code in multiple locations, think about moving server side validation code to a single location near or in the model, or as its own library project.

How validation works.
Both client and server side validation work because of a few conventions in your project that match up data annotations, Html Helpers, rendered output. Html Helpers in views render HTML elements containing attributes that start with the pattern data-val-*. The data-val-* attributes contain error messages, regular expressions, ranges, and other validation information that originates in data annotations.

That means that this decorated code in the model... 

[DisplayName("Qty Available")]
[Required(ErrorMessage = "The Qty Available field is required.")]
[Range(0,120)]
public int QtyOnHand { get; set; }

...combined with this code in the view...

<div class="editor-field">
@Html.EditorFor(model => model.QtyOnHand)
@Html.ValidationMessageFor(model => model.QtyOnHand)
</div> 

...turns into this HTML at runtime...

<div class="editor-field">
<input class="text-box single-line" 
data-val="true" data-val-number="The field Qty Available must be a number." 
data-val-range="The field Qty Available must be between 0 and 120." 
data-val-range-max="120" data-val-range-min="0" 
data-val-required="The Qty Available field is required." 
id="QtyOnHand" name="QtyOnHand" type="text" value="12" />
<span class="field-validation-valid" data-valmsg-for="QtyOnHand" data-valmsg-replace="true"></span>
</div> 

The tie-in between the data model annotations and the data-val-* attributes should be clear after reading the above code, but it's where the client side validation ties in might not be so obvious. Open the \Scripts\jquery.validate.unobtrusive.js file and search for "data-val". Right away you'll see that the JavaScript uses the data-val-*, input-* and field-* CSS classes to display/hide validation messages on the client. Although you shouldn't modify or need to maintain the built-in .js files; it's worth investigating them to see how things work together in ASP.NET MVC.

 function onError(error, inputElement) {  // 'this' is the form element

 var container = $(this).find("[data-valmsg-for='" + inputElement[0].name + "']"),

             replace = $.parseJSON(container.attr("data-valmsg-replace")) !== false;

 container.removeClass("field-validation-valid").addClass("field-validation-error"); 

error.data("unobtrusiveContainer", container);

if (replace) {

container.empty();

  error.removeClass("input-validation-error").appendTo(container);

}

else {

    error.hide();

}

}

 Having the onError function tied in by only HTML attributes keeps client side validation unobtrusive, or in other words, out of the way of your view code. Annotations combined with unobtrusive validation make both the view and the output are very clean and maintainable. The final result in the browser is fully capable client side validation that falls back to server side validation for browsers without JavaScript enabled. Either way, the same validation happens and the user sees the same error.

Summary & Code
Data annotations give you a nice way to keep validation and business logic close to the model, and easier to maintain, test and debug. Additionally, cleaner, simpler output leads to faster download speeds and easier integration with client side script libraries like jQuery for UI manipulation.

Although this post focuses on data annotations and client side script, don't forget that server side validation is very important.

you can see more information here about the same topic here Unobtrusive Client Validation in ASP.NET MVC 3

Continue Reading →