Pranay Rana: August 2011

Thursday, August 11, 2011

Build your silver light application

Any Mehtod

Here I am oging to discuss about Any method. One purpose of this method is to check either the collection has elemetn or not.
Example
List<string> members = 
         new List<string>() { "pranay", "Hemang" };
   bool ISCollectionEmpty = members.Any();             
So by using method I get to know my collection has any element or not.
So when I run above coe I get true in my boolean variable if there is not element it return flase.

Now cosider the below Database Table and  LINQ to SQL dbml file

Department table

Employee table


As you can see there is one to many relationship between Department and Employee.

Problem Statement
Now Here I want to list out only those department whihc has employee.

Solution
Most of the people do the gorup by and make use of the join and then try to find out the department which has solution.

But the better solution to this is make use of Any() method available in the System.Linq for the collection as below
var deptList = from dept in dbcontext.Departments
                           where dept.Employees.Any()
                           select dept;

   foreach (var item in deptList)
   {
      Console.WriteLine(item.Name + " : " + item.Employees.Count());
   }
Output:

As you can see the above query fetch the those department only which has employee and remove the department those doesnt have any.
I can easily able to get the cout of the employees in department using count method.

Sql query :
When you see the Sql profiler or get the query in visual studio by watching variable.
SELECT [t0].[Id], [t0].[Name]
FROM [dbo].[Department] AS [t0]
WHERE EXISTS(
  SELECT NULL AS [EMPTY]
  FROM [dbo].[Employee] AS [t1]
  WHERE [t1].[DeptId] = [t0].[Id]
)

Saturday, August 6, 2011

SqlMethod LIKE

In this post I am going to discuss about the special method available in .NET framework which allows to perform the like operation as we do in the t-sql to when searching data with string.

In sql to search string data query is
--Searching string contains abc i.e prabcfg, abcpr
Select * from table name where columnname like '%abc%'
--Searching string starts with abc i.e abcpr, abcrana
Select * from table name where columnname like 'abc%'
--Searching string ends with abc i.e prabc, ranaabc
Select * from table name where columnname like '%abc'
--Searching string with wildcard char _ i.e abec,abfc
Select * from table name where columnname like 'ab_c'
Now in LINQ to achieve same thing we have function like StartsWith, EndsWith and Contains. So LINQ query is
//Searching string contains abc i.e prabcfg, abcpr
var user = form u in users where u.Name.Contains("abc");
//Searching string starts with abc i.e abcpr, abcrana
var user = form u in users where u.Name.StartsWith("abc");
//Searching string ends with abc i.e prabc, ranaabc
var user = form u in users where u.Name.EndsWith("abc");
But with the LINQ I cannot able to achieve the last case(wildcard char _ ) and many more that I can do in like condition of the t-SQL.

SqlMehtod class has static method Like which allows to perform the same function as the like keyword of t-sql. So the query with the LINQ is
var emplist = from emp in dbcontext.Employees
where SqlMethods.Like(emp.FirstName, "pr_nay")
select emp;
When you execute the statement you can see the the t-sql statement same as above i.e wildcard statement listed above, you can view the query in the sql profiler.
But the the Like method work when you do the query using LINQ TO SQL only.

Friday, August 5, 2011

LINQ presentation

Linq
View more presentations from pranayamr