Tag Archives: C#

Update table using sub select in LINQ with titlecase

Posted on by
1

I’m not much on SQL and db so it took me 2 days to solved my problem converting sub select set update sql to LINQ.

NOTE:This is not LINQ to SQL! its pure Object to LINQ only.

            DataTable dtbl = dvPractitioners.Table;
            TextInfo titlecase = new CultureInfo("en-US", false).TextInfo;
 
            string searchExempt = "old Word~NEW WORD~Optional dropdownlist value|Old code again~new code again~Optional dropdownlist value"];
 
            string[] searchExemptSplitted = { "|" };
            if (searchExempt != string.Empty) searchExemptSplitted=searchExempt.Split(Convert.ToChar("|"));
 
            var LinQuery = (from s in dtbl.AsEnumerable()
                            join c in dtblSource.AsEnumerable() on s.Field(textfield) equals c.Field(textfield) into temp
                            from t in temp.DefaultIfEmpty()
                            orderby s.Field(textfield) ascending
 
                            let result = t.ItemArray[index1].ToString().ToLower()
 
                            let z = (from ftable in searchExemptSplitted
                                                let filter = ftable.Split('~')
                                                where result == filter[0]
                                                select
                                                result == filter[0] ? filter[1] + "~" + filter[2]:null
                                             ).Distinct().SingleOrDefault()
 
                            let zResult= z != null ? z.Split('~') : null
 
                            select new
                            {
                                textfield = z != null ? zResult[0] : titlecase.ToTitleCase(result.ToString().Trim().ToLower())
                                ,
                                datavalue = z != null ? zResult[1] : titlecase.ToTitleCase(t.ItemArray[index2].ToString().Trim().ToLower())
                            }).Distinct();
 
            drplstbox.DataSource = LinQuery;
            drplstbox.DataTextField = "textfield";
            drplstbox.DataValueField = "datavalue";
            drplstbox.DataBind();

I also modified the above code for the non-dropdown listbox and able to put it in method.

protected List FilterSearchExemptions(DataTable dtblSource,string filterField)
        {
            TextInfo titlecase = new CultureInfo("en-US", false).TextInfo;
 
            string searchExempt = ConfigurationManager.AppSettings["SearchPratitionerExemptions"];
 
            string[] searchExemptSplitted = { "|" };
            if (searchExempt != string.Empty) searchExemptSplitted = searchExempt.Split(Convert.ToChar("|"));
 
            var LinQuery = ((from s in dtblSource.AsEnumerable()
                             orderby s.Field(filterField)
                            let z = (from ftable in searchExemptSplitted
                                     let filter = ftable.Split('~')
                                     where s.Field(filterField).ToLower() == filter[0]
                                     select
                                     filter[1] + "~" + filter[2]
                                             ).Distinct().SingleOrDefault()
 
                             let zResult = z != null ? z.Split('~') : null
 
                            select
 
                            z != null ? zResult[0] : titlecase.ToTitleCase(s.Field(filterField).ToString().Trim().ToLower())
                            ).Distinct()).ToList();
            return LinQuery;
        }

If you want to return the function as datatable, use CopyToDataTable() and replace IEnumerable<datatable> LinQuery! :)