Linq Query to compare only Date part of DateTime

In this post I am going to discuss about comparing the date part of the datetime in linq query. In linq query its easy to perform datetime comparison how to compare only date not time part of datetime filed of your entity.

Let have coloseure look, In Sql Server one can do something like as below to just compare date part of the field.

SELECT * FROM dbo.tbl_MyTable
WHERE 
CAST(CONVERT(CHAR(10), DateTimeValueColumn, 102) AS DATE) = 
            CAST(CONVERT(CHAR(10),GETDATE(),102) AS DATE)

So in above query convert function did task of removing time part and only date part comparison happens.

Linq Queires
Following discussion about doing same task using linq queries.
Solution 1:
First way to achieve same thing (i.e. comparing date part) of entity or object is following

var data = context.t_quoted_value.Where(x => x.region_name == "Hong Kong" 
                  && DateTime.Compare(x.price_date.Value.Date, dt.Date) == 0)
                  .ToList();

Here Date property of DatTime used to get only date part of datetime property and made use of DateTime.Compare function to get matching object.

But the problem with this approach when make use of EnityFramework i.e. Linq To Entity , its gives following error at runtime

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

so to avoid above error in Linq to Entity query is modified to following

var data = context.t_quoted_value.Where(x => x.region_name == "Hong Kong")
                            .ToList()
                            .Where (x=> DateTime.Compare(x.price_date.Value.Date, dt.Date) == 0)
                            .ToList(); 

So in above query first data is get fetched from the database and than on list date comparison get applied. But the problem with this approach is need to load all data first than the date comparison get applied because entityframework doesn't support direct query.

Solution 2:
One more easy and simple solution to just compare datet part of datetime object is as following

            
var data1 = context.t_quoted_value.Where(x => x.region_name == "Hong Kong" 
                            && x.price_date.Value.Year == dt.Year
                            && x.price_date.Value.Month == dt.Month
                            && x.price_date.Value.Day == dt.Day).ToList();

its query use the year,month and day property of datetime object to compare date. Advantage of this solution is this is compatible with all flavor of Linq i.e. it works in linq to sql, linq to object and also in linq to enitity.

Conclusion
So the post is useful when you need to compare only date part of datetime property in Linq queries.

Leave your comments if you like it. CodeProject

Call Sql Server inbuilt functions using Linq

The post is about the the new class introduce in .net framwork for support of built in SQL-Server function. The SqlFunctions class allows to call SQL-Server function from linq queries while using EntityFramwork.

Following describes how it works
Create Edmx file i.e EntityFramwork file

 Create connection with database

Select Table(s),view(s) and Stored procedure(s)

Created EDMX file

Use SqlFunction in query
Now after deisigning the enityframwork edmx file following is way to utilize the inbuilt sql server functions in Linq queries.

public List<person> SqlTest()
        {
            using (SchoolEntities se = new SchoolEntities())
            {
                var person = from p in se.People
                             where SqlFunctions.CharIndex("a", p.FirstName) == 1
                             select p;
                return person.ToList<person>();
            }
        }

As you see in above linq query its find out all persons whose name starts with letter "a". This is easily achievable by CharIndex function of sql server as you can see in query. This function is part of SqlFunction class avilable in .net framework.

SqlFunction class inside

#region Assembly System.Data.Entity.dll, v4.0.30319
// C:\Program Files\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\Profile\Client\System.Data.Entity.dll
#endregion

using System;
using System.Collections.Generic;
using System.Data.Objects.DataClasses;

namespace System.Data.Objects.SqlClient
{
 // Summary:
    //     Provides common language runtime (CLR) methods that call functions in the
    //     database in LINQ to Entities queries.
    public static class SqlFunctions
    {
        ........list of other function that is supported by this class
        //
        // Summary:
        //     Returns the starting position of one expression found within another expression.
        //
        // Parameters:
        //   toSearch:
        //     The string expression to be searched.
        //
        //   target:
        //     The string expression to be found.
        //
        //   startLocation:
        //     The character position in toSearch where searching begins.
        //
        // Returns:
        //     The starting position of target if it is found in toSearch.
        [EdmFunction("SqlServer", "CHARINDEX")]
        public static int? CharIndex(string toSearch, string target, int? startLocation)
    }
}

As you can see SqlFuction is static class and contains static methods which calls sql server inbuilt function.
Get more information about SqlFunction class and its method on msdn at : SqlFunction

Get Last n Records using Linq to SQL

The small post is about getting last n number of record(s) from the database table or from the collection using LINQ.

SQL
To get last n number of record(s) from table I do write the following query on my table records in T-SQL.

SELECT TOP n <fields> FROM Forms WHERE <field> = <data> 
ORDER BY <field> DESC

As you see in above query important thing is using order by with desc keyword means reversing the table records - (mostly we apply the order by desc on the primary key of the table).

LINQ
In LINQ we achieve the same thing with the help of OrderByDescending function and Take function.

var qry = db.ObjectCollection
                     .Where(m => m.<field> == data) 
                     .OrderByDescending(m => m.<field>) 
                     .Take(n); 

In above LINQ query same as sql need to apply the where condition first than make collection reverse using order by function and to get top record you just need to make user of Take function.

But to get the last record for the collection you make use of FirstOrDefault function as below

var qry = db.ObjectCollection
                     .Where(m => m.<field> == data) 
                     .OrderByDescending(m => m.<field>) 
                     .FirstOrDefault(); 

Read about the method use on MSDN

• Enumerable.FirstOrDefault Method (IEnumerable)
• Enumerable.OrderByDescending Method
• Enumerable.Take Method

Article of the Day on Microsoft site Till Date

Cascading with jQuery AutoComplete

Calling Cross Domain WCF service using Jquery/Javascript

Steps to Call WCF Service using jQuery

LINQ TO SQL GridView (Enhanced Gridview)

Enhanced Textbox Control

Most efficient way to update with LINQ to SQL

Question

Can I update my employee record as given in the function below or do I have to make a query of the employee collection first and then update the data?

public int updateEmployee(App3_EMPLOYEE employee)
      {
          DBContextDataContext db = new DBContextDataContext();
          db.App3_EMPLOYEEs.Attach(employee);
          db.SubmitChanges();
          return employee.PKEY;
      }

Or do I have to do the following?

public int updateEmployee(App3_EMPLOYEE employee)
    {
        DBContextDataContext db = new DBContextDataContext();
        App3_EMPLOYEE emp = db.App3_EMPLOYEEs.Single(e => e.PKEY == employee.PKEY);
        db.App3_EMPLOYEEs.Attach(employee,emp);
        db.SubmitChanges();
        return employee.PKEY;
    }

But I don't want to use the second option. Is there any efficient way to update data?

I am getting this error by using both ways:

An attempt has been made to Attach or Add an entity that is not new, 
perhaps having been loaded from another DataContext.  
This is not supported.

Answer

I find following work around to this problem :

1) fetch and update entity (i am going to use this way because it ok for me )

public int updateEmployee(App3_EMPLOYEE employee)
    {
        AppEmployeeDataContext db = new AppEmployeeDataContext();
        App3_EMPLOYEE emp = db.App3_EMPLOYEEs.Single(e => e.PKEY == employee.PKEY);
        emp.FIRSTNAME = employee.FIRSTNAME;//copy property one by one 
        db.SubmitChanges();
        return employee.PKEY;
    }

2) disble ObjectTrackingEnabled as following

// but in this case lazy loading is not supported
       
    
        public AppEmployeeDataContext() : 
            base(global::LinqLibrary.Properties.Settings.Default.AppConnect3DBConnectionString, mappingSource)
          {
                    this.ObjectTrackingEnabled = false;
           OnCreated();
          }

3) Detach all the related objects

partial class App3_EMPLOYEE
    {
        public void Detach()
        {
            this._APP3_EMPLOYEE_EXTs = default(EntityRef);
        }
    }

     public int updateEmployee(App3_EMPLOYEE employee)
    {
        AppEmployeeDataContext db = new AppEmployeeDataContext();
        employee.Detach();
        db.App3_EMPLOYEEs.Attach(employee,true);
        db.SubmitChanges();
        return employee.PKEY;
    }

4) use Time stamp in the column

http://www.west-wind.com/weblog/posts/135659.aspx

5) Create stored procedure for updating you data and call it by db context

Find Actual Question and Answer at : http://stackoverflow.com/questions/2872380/most-efficient-way-to-update-with-linq-to-sql

DataLoadOptions and How to use in Compiled Linq query

DataLoadOption in LINQ allows immediate loading and filtering of related data. The DataLoadOption allow to load related object so this remove need of the firing subquery every time you ask for the related object(s).

Consider below case

If you do code like this

var distlist = (from d in edb.Distributors select d).ToList();
            foreach(Distributor d in distlist)
            {
              var clientlist = d.Customers;
              foreach( Customer c in clientlist)
              {
                   //do the code 
              }
            }

each time inner for loop fire query on database to get the customer related to distributor which in turn decrease the performance. But if you know in advance that you are need to use the related list when you are loading main list i.e you need to load data of related entity eagerly make use of DataLoadOptions.

Modified code is something like

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Distributorgt;(d => d.Customers);
dataContext.LoadOptions = dlo;

Note

• Be careful when you use DataLoadOption because it may decrease the performance if you are not going to use related objects. Use only in situation when you want to load related object early and going to consume it all.
  
• You an only attach DataLoadOption once with the instance of datacontext.
  

The above DataLoadOption runs perfectly when you use regular Linq Queries. But it does not work with compiled queries. When you run this code and the query hits the second time, it produces an exception:

DataLoadOptions in Complied queries
First to get more info about Complied look this post : Increase Linq query performance by Compling it
Now when you attache DataLoadOption to complied query as we did above it give you an exception at run-time
Compiled queries across DataContexts with different LoadOptions not supported

To avoid the exception you need to create the static DataLoadOption variable because as the compiled linq queries are the static one it not consume the DataLoadOption which is not static.

So for that I have created below code where GetDataLoadOpt() static function returns DataLoadOptions object and I store it into static variable dlo and than attach this dlo1 with the compiled version of query.

public static DataLoadOptions dlo1 = GetDataLoadOpt();

    public static Func<DataLoadTestDataContext, string, IQueryable<Product>>
        ProductByCategory =
        CompiledQuery.Compile((DataLoadTestDataContext db, string category) =>
        from p in db.Products where p.Category == category select p);

    public static DataLoadOptions GetDataLoadOpt()
    {
        DataLoadOptions dlo = new DataLoadOptions();
        dlo.LoadWith<Product>(p => p.ProductWithCategory);
        return dlo;
    }

    public static void testfunction()
    {
        DataLoadTestDataContext context = new DataLoadTestDataContext();
        context.LoadOptions = dlo1;
        var productlist = ProductByCategory(context, "mobile");

        foreach (Product p in productlist)
        {
            Console.WriteLine(p.ProductWithCategory);
        }
    }

If you want to get the above exception try code removing static from the function testfunction and variable dlo1 than assign it to compiled version of query you will get the run-time exception.
CodeProject

Increase Linq query performance by Compling it

Each time when we fire any Linq to SQL or to any other data-source using Entity Framework query run-time convert query to expression tree and than into t-SQL statement. So if the query get fired number of time in application it get converted in the expression tree to t-SQL statement by run-time this conversion increase execution time which in turn affect performance of the application. To avoid this cost Microsoft .net framework introduce concept of the complied queries which allows compilation and caching of queries for reuse.

Now, there is shopping website which list of the product by the category basically it allows filtering of product by the category. So if I have 100 no of user who logged in to system and do the filter the product by category they basically fire

from p in db.Products where p.Category == category select p

query to get the result they want. So this will increase the execution cost as this query get fire no of time and get converted in the expression tree and in turn get the result.

With the help of CompiledQuery class of .net framework I can rewrite my code and it's like as below

public static Func<DataLoadTestDataContext, string, IQueryable<Product>>
  ProductByCategory =
    CompiledQuery.Compile((DataLoadTestDataContext db, string category) =>
     from p in db.Products where p.Category == category select p);

Static Variable
Static variable is used to store the so its not thread safe and global to all. Because of static variable compilation will only occur once per AppDomain and will remain cached through the rest of the application's lifetime. If you don't use the static variable query get complied each time which increase the cost and decrease the performance of the application.

Constrain and Use
Cannot use to store the queries which returns Anonymous type, because the anonymous type doesn't have any type to assign generic argument of function.

Useful when query is used heavily and you want to reuse the query, by using this way increase the performance of the application.

Where to include the code ?
Better place to include above code is partial class, its extended partial class to the partial class generated by ORM tool.More:Extenend ORM generated class

CodeProject

Log your LINQ query

Most of the beginner developer who are using LINQ to SQL as there back-end to talk with the database (i.e to perform the database CRUD operation), don't have idea what the query get fire to database out of LINQ query.

Lastly I asked to log the query that fire to database out of my LINQ query. So as solution I found one make use of SQL Server Profiler to check fire query. But with the profiler I can not able to log the queries.

I fond one solution is to make use of Log property of DataContext object. Log property allow me to log the queries in the file. Consider the below code

//created temp file 
using 
(System.IO.StreamWriter sw = new System.IO.StreamWriter(@"e:\tempdatacontext.log"))
{
    EmployeeDataContext edb = new EmployeeDataContext();
    //assigned streamwriter to the log property of datacontext
    edb.Log = sw;
    var cust = from c in edb.Customers
              join d in edb.Distributors on
                new { CityID = c.CityId, StateID = c.StateId, 
                      CountryID = c.CountryId, Id = c.DistributorId }
                equals
                new { CityID = d.CityId, StateID = d.StateId, 
                      CountryID = d.CountryId, Id = d.DistributorId }
              select c;

    List<customer> custList = cust.ToList();
}

So once the code get executed it's time to check the temp file. As I opened up the file I found following query get fired on my database.

It's fun to find the query get fire to database and you get to know if there is any problem in the LINQ query you wrote.

CodeProject

Linq Join on Mutiple columns using Anonymous type

I was working on the project using LINQ. I got the requirement to join the two entity on multiple column.

For example consider the following image. There are two entity Distributor and Customer related to each other.

Now I want to find out all customer who lives in same city where the distributor living.

So to find out that I have to make join between Customer and Distributor. And to achieve this I need to join by using multiple columns City,State,Country,ID. (Note: I am using id in join because later on I want to get which distributor near to customer).

Now with the LINQ you can join two entity on multiple columns by creating one anonymous type.

EmployeeDataContext edb= new EmployeeDataContext();
var cust = from c in edb.Customers
           join d in edb.Distributors on
             new { CityID = c.CityId, StateID = c.StateId, CountryID = c.CountryId, 
                   Id = c.DistributorId }    
           equals
             new { CityID = d.CityId, StateID = d.StateId, CountryID = d.CountryId, 
                   Id = d.DistributorId }    
           select c;

Note : As anonymous types used to join entity on multiple column, so for that make sure that both are equal and they must have the same properties in same order. Otherwise it don't get complied and you get error.

Once you are done run the code and you see the following query in your sql profiler or you can also use the visual studio feature to get the query.

SELECT [t0].[Id], [t0].[Name], [t0].[EmailId], [t0].[CityId], [t0].[StateId], 
[t0].[CountryId], [t0].[PinCode], [t0].[DistributorId]
FROM [dbo].[Customer] AS [t0]
INNER JOIN 
[dbo].[Distributor] AS [t1] ON 
([t0].[CityId] = [t1].[CityId]) 
    AND ([t0].[StateId] = [t1].[StateId]) 
    AND  ([t0].[CountryId] = [t1].[CountryId]) 
    AND ([t0].[DistributorId] =[t1].[DistributorId])

CodeProject

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]
)

CodeProject

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.
CodeProject

LINQ presentation

Linq

View more presentations from pranayamr

CodeProject

Ordering data in LINQ Queries by more than one column

In this post I am going to show how to do ordering when you require to order data by using the multiple columns.

By using .Orderby(x=>x.Columnname) in the LINQ query we can easily order data of the source collection. So most of new developer make use of the same function two time .Orderby(x=>x.Columnname).Orderby(x=>x.Columnname) and thinks that will do the ordering on the multiple columns.

IEnumerable<Employee> emp = dc.Employees
                                 .OrderBy(x => x.Name)
                                 .OrderBy(x => x.Desc); 

But its always does the order by the column you specified in the last OrderBy() method.

Following is two solution to achieve
Solution 1:
Always make use of ThenBy() after OrderBy() because  OrderBy() returns an IOrderedEnumerable which then exposes the Methods: ThenBy() and ThenByDescending(). This means that we can OrderBy on multiple Fields by chaining the OrderBy() and ThenBy() together.

IEnumerable<Employee> emp = dc.Employees
                                  .ThenBy(x =< x.Name)
                                  .OrderBy(x => x.Desc);

Solution 2:
If you don't want to go for the Lamda expression where you can easily achieve the multiple ordering

var emp = from e in dc.Employees
          orderby e.Name, e.Desc
          select e;

As you can see in above statement after order by you can add the multiple columns and do the ordering on multiple columns.

CodeProject

IQueryable Vs. IEnumerable in terms of LINQ to SQL queries

Few days ago I am working on my project which is having Linq to Sql as database layer. I got requirement to get the first of all employee who's designation starts with "soft".

I get fired below query and got the result in IEnumerable varible

IEnumerable<employee> emp = 
         dc.Employees.Where(x => x.Desc.StartsWith("soft"));
emp = emp.Take(1);

But later on I found there it’s taking too much time to get the count.

So to try something else I use IQueryable to store the result and to get the count.

IQueryable<employee> emplist = 
         dc.Employees.Where(x => x.Desc.StartsWith("soft"));
emplist = emplist.Take(1);

After using IQueryable I found I get result faster than previous one.

So to find out this I used SQL Profile to find out the SQL Query fired by the code.

First block of code  fire following query i.e which use IEnumrable to store output of the LINQ query

SELECT [t0].[Id], [t0].[Name], [t0].[Address], [t0].[Desc] AS [Desc]
FROM [dbo].[Employee] AS [t0]
WHERE [t0].[Desc] LIKE @p0

Second block of code  fire following query i.e which use IQuerable to store output of the LINQ query

SELECT TOP (1) [t0].[Id], [t0].[Name], [t0].[Address], [t0].[Desc] AS [Desc]
FROM [dbo].[Employee] AS [t0]
WHERE [t0].[Desc] LIKE @p0

Major difference between both query is First one doesn't contain the TOP clause to get the first record but second one make use of the TOP to get the first record.

When I explore further I fond following difference between both ?

The major difference is that IEnumerable will enumerate all elements, while IQueryable will enumerate elements, or even do other things, based on a query. In case of IQueryable Linq Query get used by IQueryProvider  which must interpret or compiled in order to get the result.
i.e Extension methods defined for IQueryable take Expression objects instead of Func objects (which is what IEnumerable uses)., meaning the delegate it receives is an expression tree instead of a method to invoke.

IEnumerable is great for working with in-memory collections, but IQueryable allows for a remote data source, like a database or web service.

CodeProject

Dynamic query with Linq

In this post I am going to discuss about building dynamic query with the LINQ. LINQ to SQL allow user to query data from the database without writing sql queries by writing LINQ queries. LINQ represent each table as one entity and where LINQ queries allows to manipulate data in type safe.

But Static LINQ queries not able to meet all our programming needs. A Dynamic LINQ queries is needed when we need to retrieve a set of records based on different search parameters.

For example - An employee search screen or a general purpose report which needs to execute a different SELECT statement based on a different WHERE as well as Sorting column to sort data.

Dynamic query in Sql server

In SQL there is concept of dynamic queries which allow to write and execute dynamic queries easily. In SQL server we use EXECUTE or sp_executesql to execute dynamic query.
For example:

DECLARE @SQLQuery AS NVARCHAR(500)
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = + convert(@EmpID as varchar(10))
EXECUTE(@SQLQuery)   

or

DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @EmpID INT
SET @EmpID =100
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID'
SET @ParameterDefinition =  '@EmpID INT'
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

What to do when I am using LINQ ?
There are two way to achieve this thing easily
1. Use Dynamic LINQ libarary
2. Use PredicateBuilder

To understand both of the above library consider the below screen shot

I want to search data by entering in above screen. Note here I may left some field blank and some field filled with value.

Use Dynamic LINQ library
Dynamic LINQ library allows build query which are having varying where clause or orderby. To work with the dynamic LINQ library you need to download and install file in your project.
you get the file from this link : Basic Dynamic LINQ C# Sample

so once you install the file you can build query as we can do in pl-sql query.

Following code demonstrate how to build where dynamically

string strWhere = string.Empty;
        string strOrderBy = string.Empty;

        if (!string.IsNullOrEmpty(txtAddress.Text))
            strWhere = "Address.StartsWith(\"" + txtAddress.Text + "\")";  
        if (!string.IsNullOrEmpty(txtEmpId.Text))
        {
            if(!string.IsNullOrEmpty(strWhere ))
                strWhere = " And ";
            strWhere = "Id = " + txtEmpId.Text;
        }
        if (!string.IsNullOrEmpty(txtDesc.Text))
        {
            if (!string.IsNullOrEmpty(strWhere))
                strWhere = " And ";
            strWhere = "Desc.StartsWith(\"" + txtDesc.Text + "\")";
        }
        if (!string.IsNullOrEmpty(txtName.Text))
        {
            if (!string.IsNullOrEmpty(strWhere))
                strWhere = " And ";
            strWhere = "Name.StartsWith(\"" + txtName.Text + "\")";
        }

        EmployeeDataContext edb = new EmployeeDataContext();
        var emp = edb.Employees.Where(strWhere);
        grdEmployee.DataSource = emp.ToList();
        grdEmployee.DataBind();

In above code I am building strWhere dynamically because there may be some criteria no have value where some has.


Predicate Builder
Predicate builder works same as dynamic linq library but the main difference is its allow to write more type safe queries easily.
You can get the detail about predicate builder form here : Dynamically Composing Expression Predicates
Following code shows how you can use PredicateBuilder easily to create dynamic clause easily.

var predicate = PredicateBuilder.True();

        if(!string.IsNullOrEmpty(txtAddress.Text))
            predicate = predicate.And(e1 => e1.Address.Contains(txtAddress.Text));
        if (!string.IsNullOrEmpty(txtEmpId.Text))
            predicate = predicate.And(e1 => e1.Id == Convert.ToInt32(txtEmpId.Text));
        if (!string.IsNullOrEmpty(txtDesc.Text))
            predicate = predicate.And(e1 => e1.Desc.Contains(txtDesc.Text));
        if (!string.IsNullOrEmpty(txtName.Text))
            predicate = predicate.And(e1 => e1.Name.Contains(txtName.Text));

        EmployeeDataContext edb= new EmployeeDataContext();
        var emp = edb.Employees.Where(predicate);
        grdEmployee.DataSource = emp.ToList();
        grdEmployee.DataBind();

So as you see in above code I had created one PredicateBuilder for AND condition and building where clause same way you can build OR clause by using PredicateBuilder.

Difference between both library

• Predicatebuilder allows to build TypeSafe dynamic queries.
• Dynamic LINQ library allows to builder query with the Dynamic ORDER BY clause.

Note : Above difference is based on the experience that I have with both library. If you know more than please comment so that I can include in my list.

CodeProject

Difference between two Entity Set using LINQ

In this post I am going to discuss about how to get the difference between two table/ entity sets For Ex. we want to get rows form table_A which are not exists in table_B. (When table_A primarykey is part of table_B foreignkey).

Following is way to get the row(s) form table_A which are not part of table_B

SELECT  
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

In Linq to Sql you can achieve same thing just by calling one function Except.

var infoQuery =
    (from tblA in db.Table_A
    select tblA.Key)
    .Except
        (from tblB in db.Table_B
        select tblB.Key);

So by using above query you can easily achieve task of getting difference between set of collection.

CodeProject

Find duplicate with Linq

In this small post I am going to discuss about how to get list of duplicate items for the collection that we do in sql.
For example I have to get list of the email id which is get entered in user table more than one time.

SELECT email, 
  COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Linq query work same as on the set of collection and which make use of count

DataClassesDataContext db = new DataClassesDataContext();
var duplicates = db.Users
    .GroupBy(i => i.emailid)
    .Where(g => g.Count() > 1)
    .Select(g => g.emailid);
foreach (var d in duplicates)
    Console.WriteLine(d);

So by using above query you can easily achieve task of finding duplicate for your set of collection.
CodeProject

Sql Where In with Linq

In my current project I am using Linq To Sql ORM as my database layer for doing database operation. Now as I am moving further I got requirement to that I have to filter one table record form another table. For example I have to get list of employee which are either ProjectManger or TeamLead.

Sql query to for this is

select * 
from Employee
where deptid in (select deptid from Department)

To do same thing in Linq To Sql you need to use Contains() on one set of record function as you can see below

DataClassesDataContext db = new DataClassesDataContext();
List>p;int< lstDept = db.Department.Select(x => x.Dept_PKEY).ToList>p;int<();

var qry = from emp in db.Employees
where lstDept.Contains(item.Dept_PKEY)
select emp;

Above example shows how easily you can achieve WHERE IN functionality. Same you can achieve when you are coding using the set of collection object. Enjoy Linq...

CodeProject

Linq Joins with SelectMany

SelectMany projects each element of a sequence to an IEnumerable and flattens the resulting sequences into one sequence. In this post I am going to show how you can use SelectMany extension method to achieve the join between related tables easily without writing long queries.

To understand this consider example below

As shown in above image customer is having relation with the order table.

Scenario 1 
Find all of the users who have an order.

To achieve the above requirement, you need to apply an inner join between the tables. The Linq query to do this is:

var CustomerWithOrders = from c in Customers
  join from o in c.Orders
    select new { 
           c.Name, 
           p.Description, 
           o.Price 
          }

The query above inner joins the customer table with the order table and returns those customer having orders.

The shorter way to achieve the above is to use SelectMany:

Customers.SelectMany (
      c => c.Orders, 
      (c, o) => 
         new  
         {
            Name = c.Name, 
            Description = o.Description, 
            Price = o.Price
         }
   ) 

Scenario 2
Find all of the users who have an order and display N/A for the users who do not.

To achieve the above requirement, you need to apply an Outer join between the tables. The Linq query to do this is:

var CustomerWithOrders = from c in Customers
 join from o in c.Orders.DefaultIfEmpty()
 select new { 
         Name =c.Name,
         Description = ( o.Description ?? "N/A"), 
         Price = (((decimal?) o.Price) ?? 0)  
        }

The query above outer joins the customer table with the order table and returns all of the customers with or without orders.

But with the SelectMany, the query is:

Customers.SelectMany (
      c => c.Orders.DefaultIfEmpty (), 
      (c, o) => 
         new  
         {
            Name = c.Name, 
            Description = (o.Description ?? "N/A"), 
            Price = ((Decimal?)(o.Price) ?? 0)
         }
   ) 

Summary
SelectMany is a way to flatten several results into a single sequence.
CodeProject

(Linq and Nullable Values) OR ( SQL ISNULL with LINQ)

Here in this small post I am going to show you, how you can deal with the Nullable values in LINQ queries and how you can achieve functionality like SQL ISNULL function.
Read following post before continuing with this

• SQL to LINQ ( Visual Representation )
• Coalescing operator - ??

Problem

I am dealing with the LINQ queries, In which I have to diplay "N/A" were the value is null for the given property/column.

Solution 1
You can use ternary operator as shwon in below example. Here MobileNo = "N/A" for the null values

var user = from u in Users
join uc in UserClients on u.Id equals uc.UserId
into myuserwithclient
from m in myuserwithclient.DefaultIfEmpty()
select new {
 Id=u.Id,
 FirstName=u.FirstName,
 LastName=u.LastName,
 UserId=m.UserId,
 MobileNo = (m.MobileNo == null) ? "N/A" : m.MobileNo
};

Solusion 2
Use special Coalescing operator operator (??) as showin in below example. Here MobileNo = "N/A" for the null values

var user = from u in Users
join uc in UserClients on u.Id equals uc.UserId
into myuserwithclient
from m in myuserwithclient.DefaultIfEmpty()
select new {
 Id=u.Id,
 FirstName=u.FirstName,
 LastName=u.LastName,
 UserId=m.UserId,
 MobileNo = m.MobileNo  ?? "N/A" 
};

Summary
Above solution shows how easily we handle null value as well as achieve functionality of the SQL ISNULL function.