Category Archives: MSDevAdminSec

Posts for Microsoft SQL Database Development , Administration and Security.

Essential LINQ For Beginners 2 | WHERE

Posted on by
4

WHERE IN LINQ

Yesterday , we talk about how LINQ , using C# language , can select numbers the way SQL SELECT statement does. Today , we will go over examples of WHERE statement using LINQ. You can find the article showing LINQ with SELECT here. Hopefully after 2 or 3 more examples of LINQ statements , we can being to see the beauty of having the ability to interact with SQL Databases within the language directly. Lets take a look at the example then ,

LINQ Example 

public void LinqWhere()
{
    int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };

    var lowNums =
        from n in numbers
        where n < 5
        select n;

    Console.WriteLine("Numbers < 5:");
    foreach (var x in lowNums)
    {
        Console.WriteLine(x);
    }
}

As you might have guesses by simply reading the code , it take an array of integers , named numbers , from from that array , it selects those numbers less than 5 and pass it to another array. Then uses a “foreach” loop to prints out the list on each new line by itself.

You might ask what is this VAR type. It doesn’t seem to be an integer or String or anything we have seen before. Here is the definition of it from Microsoft website ,

“Beginning in Visual C# 3.0, variables that are declared at method scope can have an implicit type var. An implicitly typed local variable is strongly typed just as if you had declared the type yourself, but the compiler determines the type.”

It then gives two examples of using VAR and integer ,

var i = 10; // implicitly typed
int i = 10; //explicitly typed

Basically , instead of saying this is an integer , you let the compiler does the job for you. Both statements above are equal as far as your program is concerned.

LINQ WHERE Conclusion

Hope it helps someone who is struggling with LINQ and also hope you enjoy the read. Its been a great Sunday for me and cya tomorrow! There will be more LINQ!

Essential LINQ For Beginners

Posted on by
2

Essential LINQ

Alrighty , I shall now take a dive into LINQ from today onwards. SO what exactly is LINQ in the first place? This is one definition ,

“LINQ (Language Integrated Query) is a Microsoft programming model and methodology that essentially adds formal query capabilities into Microsoft .NET-based programming languages. LINQ offers a compact, expressive, and intelligible syntax for manipulating data. The real value of LINQ comes from its ability to apply the same query to an SQL database, a DataSet, an array of objects in memory and to many other types of data as well. LINQ requires the presence of specific language extensions.

LINQ uses an SQL-like syntax to make query expressions well beyond the capabilities of embedded SQL as implemented in programming languages. That’s because embedded SQL uses a simplified, streamlined syntax to add SQL statements to other programming languages, where there’s no attempt to integrate such statements into the native syntax and typing mechanisms. Thus, you can’t invoke native language structures such as functions in embedded SQL statements, as you can using LINQ, because it is implemented to use native syntax, structures, and typing mechanisms. Furthermore, LINQ may be used to access all kinds of data, whereas embedded SQL is limited to addressing only databases that can handle SQL queries.”

Source Here.

LINQ Example

Well then what does it look like?

It looks like this ,

public void LinqExample()
{
    int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };

    var numsPlusPlus =
        from n in numbers
        select n + 1;

    Console.WriteLine("Numbers++:");
    foreach (var i in numsPlusPlus)
    {
        Console.WriteLine(i);
    }
}

and this prints out , 

Numbers++:
6
5
2
4
10
9
7
8
3
1

If you remember or read the post on loops I did a while back , here is the link, you will notice that it looks very similar to the for loop we did back then , except that instead of going through numbers from start to finish , I went through a group or an array of lists.

An array is simply a container of objects such as an integers we have over here. One of the main usage is to have the group sliced and diced. You could for example , 5th place of an array or maximum values of the list. Of course , a table is also an array of data. You could select the maximum value in a specific column or choose a specific value. You could imagine here that there is a table with a column header named “numbers” and the values are entered as follow ,

Numbers
5
4
1
3
9
8
6
7
2
0

Then you would do a select all statement and then add 1 to the result. The result would be similar to above and that is one of the reason you would see such statements ,

var numsPlusPlus =
        from n in numbers
        select n + 1;

This in SQL would be

Select *  from numbers;

Hmms … I can’t seems to find a function that adds a value to the select output .. But you get the drift. This is where we will be starting out on LINQ and I hope to have more examples in future. Cya!

SELECT Statement with AND & OR Operators

Posted on by
4

Lets take a breather from JOIN statements we been looking at and lets look at AND and OR operators to be used in conjunction with with the SELECT statement to query information from the database.

Here is a sample SELECT statement using WHERE clause


select emp_name as 'Name', emp_sex as 'Sex', emp_salary as 'Salary' from employee where emp_sex = 'Male';

This will return you with 3 rows , excluding the lone female we have in the sample database.

Name Sex Salary
Michael Bay Male 90000.00
John Evan Male 5000.00
George Chan Male 50000.00

Its all fine but what if you want to select those who are BOTH Male AND has salary of more than 10000.00? Of course , this mean only two out of three will qualify. SO how do we do this in SQL?


select emp_name , emp_sex, emp_salary from employee where emp_sex = 'Male' and emp_salary > 5000;

Here we are looking for employees who are both Male and must have salary of more than 5000. The important thing to note is that using “AND’ means both the conditions must be satisfied. The query result for this statement is of just 2 rows.

Name Sex Salary
Michael Bay Male 90000.00
George Chan Male 50000.00

So what about OR? Try it. Replace ‘and’ with ‘or’ in the previous statement , like this ..


select emp_name , emp_sex, emp_salary from employee where emp_sex = 'Male' or emp_salary > 5000;

Guess what is the result from this query? If you say all three rows are returned as the first query then you are right. Because using ‘OR’ operator , only one of the conditions must be right. Either the employee is Male or has salary more than 5000. Since John Evan is Male , it qualifies him even though his salary is not more than 5000.

Hope this helps if you are wondering how is it possible to apply two conditions in SQL Select statement. See ya!