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?