Sample LINQ Queries

DBDataContext objeDC =new DBDataContext();


1. How to check whether the UserID exist in the table using LINQ?

UserMst objUserMst =null;
objUserMst= objeDC.UserMsts.SingleOrDefault(Rec => Rec.UserMstID == "sample");
if(objUserMst !=null)
//exists
else
// Not exists

PwdHistory objHistory = new PwdHistory();
objHistory.UserName = "Sample";
objHistory.UserPwd = FormsAuthentication.HashPasswordForStoringInConfigFile("password", "MD5");
objeDC.PwdHistories.InsertOnSubmit(objHistory);
objeDC.SubmitChanges();

FormsAuthentication.HashPasswordForStoringInConfigFile("password", "MD5");

4. use Transaction in LINQ (Roll and Commit)

DBTransaction objDBTrans=null;
objDBTrans = objeDC.Connection.BeginTransaction();
objeDC.Transaction = objDBTrans ;
try
{
       PwdHistory objHistory = new PwdHistory();
       objHistory.UserName = "Sample";
       objHistory.UserPwd = FormsAuthentication.HashPasswordForStoringInConfigFile("password", "MD5");
       objeDC.PwdHistories.InsertOnSubmit(objHistory);
       objeDC.SubmitChanges();
       objDBTrans.Commit();
}
catch(Exception ex)
{
       objDBTrans.RollBack();
}

5. Simple where condition using LINQ

var Result = from objLogin in objeDC.LoginLogs
where objLogin.UserMstID == strLoginId && objLogin.LoginEventID == "02"
select objLogin;

objUserMst = objeDC.UserMsts.SingleOrDefault(Rec => Rec.UserMstID == "sample");
if (objUserMst != null)
{
        objUserMst.eInsInd = true;
        objeDC.SubmitChanges();
}

var Result = from objUserGroup in objeDC.UserGroups
select objUserGroup;
DropdownList1.DataTextField = "Dsc";
DropdownList1.DataValueField = "UserGroupID";
DropdownList1.DataSource = Result;
DropdownList1.DataBind();

var Result = from objNewsMast in objeDC.NewsMasts
orderby objNewsMast.TimeStamp
select objNewsMast;
gvNewsImage.DataSource = Result;
gvNewsImage.DataBind();

var Result = from objMenu in objeDC.Menus
select new
{
          MenuID = objMenu.MenuID.ToString().Contains(".aspx") == true?objMenu.MenuID :
           string.Empty ,
           Dsc = objMenu.Dsc,
};

UserGroups objUserGrp = null;
objUserGrp = objeDC.UserGroups.SingleOrDefault(Rec => Rec.UserGroupID == "stringval");
if (objUserGrp != null)
{
     objeDC.UserGroups.DeleteOnSubmit(objUserGrp);
     objeDC.SubmitChanges();
}

11. Delete multiple records based on condition using LINQ?

objeDC.MenuAccesses.DeleteAllOnSubmit(objeDC.MenuAccesses.Where(Tmpr => Tmpr.MenuID == strMenuID));
objeDC.SubmitChanges();

ex.. Convert Datetime column (MM/dd/YYYY) to dd/MM/yyyy

var objResult = from objTemp in
( from objNewsMast in objeDC.NewsMasts
   select new
   {
       Date = objNewsMast.TimeStamp,
      Title = objNewsMast.Title,
    }).ToList()
select new
{
    Date = objTemp.Date.ToString("dd/MM/yyyy"),
   Title = objTemp.Title,
});

13. convert LINQ Results to DataTable

var ResultProgram = from objProgram in objeDC.Programs
         select objProgram;
         DataTable dtProgram = LINQToDataTable(ResultProgram);

14. LINQ to DataTable

public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
{
      DataTable dtReturn = new DataTable();
       PropertyInfo[] oProps = null;
       if (varlist == null) return dtReturn;
          foreach (T rec in varlist)
         {
          if (oProps == null)
         {
            oProps = ((Type)rec.GetType()).GetProperties();
             foreach (PropertyInfo pi in oProps)
             {
                  Type colType = pi.PropertyType;
                   if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                 colType = colType.GetGenericArguments()[0];
                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
             }
         }
       DataRow dr = dtReturn.NewRow();
         foreach (PropertyInfo pi in oProps)
           {
            dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
            (rec, null);
           }
         dtReturn.Rows.Add(dr);
}
return dtReturn;
}

 12. Format the retrieve column using LINQ) 10. Delete a record using LINQ?9. Check the column value having specified characters using LINQ?8. Bind into gridview using LINQ?7. Bind into DropdownList using LINQ?6. Update Single value to particular record using LINQ 3. Encrpted Password2. insert Values into table using LINQ?

Partitioning Operators

Partitioning Operators are

1. Take
2. Skip
3. TakeWhile
4. SkipWhile

Example SQL Table is UserMsts

Fields  : UserMstID, Name, EMail, CreateBy, CreateDate , Address

1. Take Simple Query

var Result = from objUserMst in UserMsts

select objUserMst;
foreach (var n in Result.Take(8))
Console.WriteLine (n.UserMstID + n.Name );

This Query uses Take to get only the first 8 records.

2. Skip

var Result = from objUserMst in UserMsts

         select objUserMst;

foreach (var n in Result.Skip(3))

Console.WriteLine ("UserId="+n.UserMstID + "; Name = "+ n.Name );

This Query uses to SKIP first three record and retrieve rest of the records.

3. Retrieve records from 10 - 20 for the following results

                Records

UserId=
UserId=admin
UserId=AZIZUL
UserId=D00281-000
UserId=D00304-000
UserId=D00611-000
UserId=D01619-000
UserId=D01745-000
UserId=D02669-000
UserId=D02670-0001
UserId=D02673-000
UserId=D02684-000
UserId=D02940-000
UserId=D03002-000
UserId=D03252-000
UserId=D06124-000
UserId=D09533-000
UserId=D09999-001
UserId=D10051-001
UserId=D10059-000
UserId=D11719-000
UserId=D12345-000
UserId=D99999-000
UserId=d99999-999
UserId=DAVID
UserId=Jason
UserId=mradmin
UserId=SOW
UserId=tech_it
UserId=TEST
UserId=test1
UserId=testing
UserId=Testing1

Query:
                   var Result = from objUserMst in UserMsts

select objUserMst;
foreach (var n in Result.Skip(10).Take(10))
Console.WriteLine ("UserId="+n.UserMstID );

Results:

UserId=D02673-000

UserId=D02684-000
UserId=D02940-000
UserId=D03002-000
UserId=D03252-000
UserId=D06124-000
UserId=D09533-000
UserId=D09999-001
UserId=D10051-001
UserId=D10059-000

4. TakeWhile

int[] numArray = { 1235, 4456, 1234, 3232, 1900, 80000, 6234, 7123, 2343,0 };

var FindNumbers = numArray.TakeWhile(n => n < 3232);
foreach (var objFindNumbers in FindNumbers)
{
Console.WriteLine(objFindNumbers);
}

         The above query  TakeWhile to return elements starting from the beginning of the array whether the given(3232)  number is hit and retrieved less than 3232 numbers from the array


Output:

1235

How to convert the string to DateTime ?

Common Function:

Public Function ConvertoDate(ByVal dateString As String,
 ByRef result As DateTime) As DateTime   
Try             
     Dim supportedFormats() As String = New String() 
{"dd/MM/yyyy", "MM/dd/yyyy", "MM/dd/yy", 
"ddMMMyyyy", "dMMMyyyy"}             
     result = DateTime.ParseExact(dateString, 
supportedFormats, System.Globalization.CultureInfo.
CurrentCulture, System.Globalization.DateTimeStyles.None)  
     Return result    
Catch ex As Exception   
    Return Nothing      
End Try    
End Function 
We should enter the Date below format. Then only it 
will convert into DateTime.

Dim supportedFormats() As String = New String() {"dd/MM/yyyy", 
"MM/dd/yyyy", "MM/dd/yy", "ddMMMyyyy", "dMMMyyyy"}


Calling the function


ConvertoDate(frmDate, TfrmDate)

frmDate - String

TfrmDate - DateTime object

Error : "The query contains references to items defined on a different data context " in LINQ queries

Solution

can't do a join across servers You could have two separate LINQ queries, one against each server, and then join those two together:

Examples

var q1 = // get your data from your first server

var q2 = // get your data from your second server

var ResultData = from r1 in q1

join r2 in q2 on r1.Key equals r2.Key

select new { r1.Value1, r2.Value2 };

Real Time Examples

First DataBase Query

var WNotMast = (from objClmType in objWebNotDC.WNotMasts
select new

{

objClmType.TimeStamp,

objClmType.WUserKey,

objClmType.Id

}).ToList();

Second Database Query

var WUsers = (from objUsers in objRegDC.WUsers

join objUsrGrp in objRegDC.WUserGroups on
objUsers.WUserGroupKey equals objUsrGrp.WUserGroupKey

select new

{

objUsers.Name,

objUsers.WUserKey,

objUsers.WUserGroupKey,

objUsrGrp.Dsc

}).ToList();

Joined Both Database result

var Result = (from objResult in WNotMast

join objUsr in WUsers on objResult.WUserKey equals
objUsr.WUserKey

where objResult.TimeStamp > dtStartDt &&



orderby objUsr.Dsc

select new

{

ID = objResult.Id,

Time = objResult.TimeStamp,

Dsc = objUsr.Dsc,

UserKey = objUsr.WUserKey,

Name = objUsr.Name


}).ToList();