Linq To Sql Introduction and Creating NorthWind Database

LINQ to SQL provides a runtime infrastructure for managing relational data as objects without losing the ability to query. Your application is free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically.



Advantages L2S offers:


No magic strings, like you have in SQL queries
Intellisense
Compile check when database changes
Faster development
Unit of work pattern (context)
Auto-generated domain objects that are usable small projects
Lazy loading.
Learning to write linq queries/lambdas is a must learn for .NET developers.


Regarding performance:
Most likely the performance is not going to be a problem in most solutions. To pre-optimize is an anti-pattern. If you later see that some areas of the application are to slow, you can analyze these parts, and in some cases even swap some linq queries with stored procedures or ADO.NET.
In many cases the lazy loading feature can speed up performance, or at least simplify the code a lot.


Regarding debuging:
In my opinion debuging Linq2Sql is much easier than both stored procedures and ADO.NET. I recommend that you take a look at Linq2Sql Debug Visualizer, which enables you to see the query, and even trigger an execute to see the result when debugging.
You can also configure the context to write all sql queries to the console window, more information here


Regarding another layer:
Linq2Sql can be seen as another layer, but it is a purely data access layer. Stored procedures is also another layer of code, and I have seen many cases where part of the business logic has been implemented into stored procedures. This is much worse in my opinion because you are then splitting the business layer into two places, and it will be harder for developers to get a clear view of the business domain.




For Linq to Sql Session we will be working on Sql 's Northwind Database. 


Creating The Northwind Database:
You can Download The database from here: NorthWind  


The Complete backup of Northwind database:  Northwind

Now Next thing is to Create a New LINQ to SQL Data Model.
For all the Next Examples you will need This Class.

1. Create a  New Project Name is LinqToSqlExample.
2. Now Go To Project  -> Add New Item and Add a Linq To Sql File and Name it NorthWind.

3. You will see a screen like below.

Now we will add a Data Connection as shown in above figure in left pane.

Select Add Connection.. And you will see below screen.

Press ok and you will see below screen.

And now our class is created . we will be using this in our next examples.

Linq Example 45 -SequenceEqual

Linq Query:

//EqualAll
            string[] str1 = { "a", "b", "c" };
            string[] str2 = { "a", "b", "c" };

            bool IsEqual = str1.SequenceEqual(str2);
            Console.WriteLine(IsEqual);

            string[] str3 = { "a", "b", "c" };
            string[] str4 = { "b", "c", "a" };

            bool IsEqual1 = str3.SequenceEqual(str4);
            Console.WriteLine(IsEqual1);

//

Output:

True
False

Explanation:


Both the arrays must be equal in sequence.

Linq Example 44 - Concat

Linq Query:

//Concat
            string[] str1 = { "a", "b", "c"};
            string[] str2 = { "d", "e", "f" };

           var allstr = str1.Concat(str2);

           foreach (var t in allstr)
           {
               Console.WriteLine(t);
           }

//

Output:

a
b
c
d
e
f

Linq Example 43 - Aggregate Seed

Linq Query:

//Aggregate Seed

            double startBalance = 100.0;

            int[] attemptedWithdrawals = { 20, 10, 40, 50, 10, 70, 30 };

            double endBalance =
                attemptedWithdrawals.Aggregate(startBalance, (Curbalance, nextWithdrawal) =>
                        ((nextWithdrawal <= Curbalance) ? (Curbalance - nextWithdrawal) : Curbalance));

            Console.WriteLine("Ending balance: {0}", endBalance);

//

Output: 

Ending balance: 20

Explanation:


This one is quite Tricky.

We start From startBalance which is 100.

Next we try to attempt to withdraw some amount from this 100. Our attemp amount is in attemptedWithdrawals array.

First We attempt to Withdraw 20 . As ( 20 < 100 ) is true we Approve the Withdraw and now Our Balance is 100-20 = 80.

Next We attempt to Withdraw 10 . As ( 10 < 80 ) is true we Approve the Withdraw and now Our Balance is 80-10 = 70.

Next We attempt to Withdraw 40 . As ( 40 < 70 ) is true we Approve the Withdraw and now Our Balance is 70-40 = 30.

Next We attempt to Withdraw 50 . As ( 50 is not less than 30 ) we wont Approve the Withdraw and now Our Balance is same as before = 30.

Next We attempt to Withdraw 10 . As ( 10 < 30 ) is true we Approve the Withdraw and now Our Balance is 30-10 = 20.

Next We attempt to Withdraw 70 . As ( 70 is not less than 20 ) we wont Approve the Withdraw and now Our Balance is same as before = 20.

Next We attempt to Withdraw 30 . As ( 30 is not less than 20 ) we wont Approve the Withdraw and now Our Balance is same as before = 20.

So the Final Balance is 20 stored in endBalance.

Linq Example 42 - Aggregate

Linq Query:

//Aggregate

            double[] doubles = { 1.7, 2.3, 1.9, 4.1, 2.9 };
            double product = doubles.Aggregate((CurrentValue, NextValue) => CurrentValue  * NextValue);            

            Console.WriteLine("Total product of all numbers: {0}", product);

//

Output:

Total product of all numbers: 88.33081

Linq Example 41 - Average Projection

Linq Query:

//Average Projection

            string[] words = { "GodOfWar", "CallOfDuty", "AssasinCreed" };

            double avgWordLeng = words.Average(w => w.Length);

            Console.WriteLine("Average word Length is {0} characters long.", avgWordLeng);

//

Output:

Average word Length is 10 characters long.

Linq Example 40 - Average

Linq Query:

//Average

            int[] myNumbers = { 5, 8, 6, 2, 1, 9, 4, 6 };

            double avg = myNumbers.Average();

            Console.WriteLine(avg);

//

Output:

5.125

Linq Example 39 - Max Projection

Linq Query:

//
            string[] words = { "GodOfWar", "CallOfDuty", "AssasinCreed" };

            int LargestWord = words.Max(w => w.Length);

            Console.WriteLine("The largest word is {0} characters long.", LargestWord);

//

Output:

The largest word is 12 characters long.

Linq Example 38 - Max

Linq Query:

//Max

            int[] myNumbers = { 5,8,6,2,1,9,4,6 };
            
            int Max = myNumbers.Max();

            Console.WriteLine(Max);

//

Output:

9

Linq Example 37 - Min Projection

Linq Query:

// Min Projection

            string[] words = { "GodOfWar", "CallOfDuty", "AssasinCreed" };

            int shortestWord = words.Min(w => w.Length);

            Console.WriteLine("The shortest word is {0} characters long.", shortestWord);

//

Output:

The shortest word is 8 characters long.

Linq Example 36 - Min

Linq Query:

//Minimum
            int[] intNumbers = { 1, 2, 3, 55, 99, 74, 102,0 };

            int min = intNumbers.Min();

            Console.WriteLine(min);

//

Output:

0

Linq Example 35 - Sum Projection

Linq Query:

//Sum Projection
            string[] s = { "abc", "abcd", "ab", "a" };

            int totalChars = s.Sum(n => n.Length);

            Console.WriteLine(totalChars);

//

Output:

10

Linq Example 34 - Sum

Linq Query:

//Sum
            int[] intNumbers = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
            int sum = intNumbers.Sum();
            Console.WriteLine(sum);

//

Output:

55

Linq Example 33 - Count Conditional

Linq Query:

//Count Conditional
            int[] intNumbers = { 1, 2, 3, 55, 99, 74, 102 };

            int total = intNumbers.Count(n => n < 10 ); //Total 3 Items which are less then 10

            Console.WriteLine(total);

//

Output:

 3 

 Total 3 Items which are less then 10

Linq Example 32 - Count

Linq Query:

//count
            int[] intNumbers = { 1, 2, 3, 55, 99, 74, 102 };

            int total = intNumbers.Count(); //Total 7 Items

            Console.WriteLine(total);

//

Output:

7

Linq Example 31 - All

Linq Query:

//all
            int[] intNumbers = { 1, 2, 3, 55, 99, 74, 102 };

            bool blnLessThan200 = intNumbers.All(n => n <= 200);

            Console.WriteLine(blnLessThan200);

//

Output:

True

It will simply check if all the elements in array are less than 200.

Linq Example 30 - Any

Linq Query: 

 //Any
            int[] intNumbers = { 1, 2, 3, 55, 99, 74, 102 };

            bool bln100to200 = intNumbers.Any(n => (n <= 200 && n >= 100));

            Console.WriteLine(bln100to200);

//

Output:

True

As you can see we want to find if any number is between 100 and 200. All the number except 102 are below 100. so our query return true as it find 102 that satisfy the condition.

Linq Example 29 - Repeat

Linq Query:

//Repeat
            var Repeated = Enumerable.Repeat(4, 3); //Repeats 4 three times
            foreach (var t in Repeated)
            {

                Console.WriteLine(t);

            }

Output:

4
4
4

Linq Example 28 - Range

Linq Query:

//Range
            var evenOddNumbers = 
                from n in Enumerable.Range(1,30)
                select new { Number=n, IsEven = (n % 2 ==0) ? "Even": "Odd"  };

            foreach (var t in evenOddNumbers)
            {
              
   Console.WriteLine(" Number:  {0}  IsEven : {1}", t.Number, t.IsEven); 

            }

Output:

Number:  1  IsEven : Odd
 Number:  2  IsEven : Even
 Number:  3  IsEven : Odd
 Number:  4  IsEven : Even
 Number:  5  IsEven : Odd
 Number:  6  IsEven : Even
 Number:  7  IsEven : Odd
 Number:  8  IsEven : Even
 Number:  9  IsEven : Odd
 Number:  10  IsEven : Even
 Number:  11  IsEven : Odd
 Number:  12  IsEven : Even
 Number:  13  IsEven : Odd
 Number:  14  IsEven : Even
 Number:  15  IsEven : Odd
 Number:  16  IsEven : Even
 Number:  17  IsEven : Odd
 Number:  18  IsEven : Even
 Number:  19  IsEven : Odd
 Number:  20  IsEven : Even
 Number:  21  IsEven : Odd
 Number:  22  IsEven : Even
 Number:  23  IsEven : Odd
 Number:  24  IsEven : Even
 Number:  25  IsEven : Odd
 Number:  26  IsEven : Even
 Number:  27  IsEven : Odd
 Number:  28  IsEven : Even
 Number:  29  IsEven : Odd
 Number:  30  IsEven : Even

Linq Example 27 - Element At

Linq Query:

//Element At
            int[] intNums = { 1, 2, 3, 4 };

            var myNums = intNums.ElementAt(2); //means strating from 0 then 1 and then 2.

            Console.WriteLine(myNums);

//

Output:

3