Use of SqlMethod in Linq

LimeDataClassesDataContext db = new LimeDataClassesDataContext();





exist = (from p in db.tblProducts

where p.IsActive == true

&& p.IsDeleted == false

&& (ptype==1?SqlMethods.Like(p.ProductCode, "%" + serachtext + "%")

: SqlMethods.Like(p.ProductName.Replace("?", "").Replace("<", "").Replace(">", "").Replace(" ", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace(":", "").Replace(".", "").Replace(";", "").Replace("\\", "").Replace("&", "").Replace("/", "").Replace("\"", ""), "%" + serachtext + "%"))

select p);

if (exist != null && exist.Count() > 0)



{

result = from p in db.vw_allproducts

where

(ptype==1? SqlMethods.Like(p.ProductCode, "%" + serachtext + "%"):

SqlMethods.Like(p.ProductName.Replace("?", "").Replace("<", "").Replace(">", "").Replace(" ", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace(":", "").Replace(".", "").Replace(";", "").Replace("\\", "").Replace("&", "").Replace("/", "").Replace("\"", ""), "%" + serachtext + "%"))

&& p.IsActive == true && p.IsDeleted == false

group p by p.ProductId into gp

select new ProdCat



{

ProductCode = gp.First().ProductCode,

ProductId = gp.First().ProductId,

ProductName = gp.First().ProductName,

ProductSummary = gp.First().ProductSummary,

BrandId = gp.First().BrandId,

SubCategoryId = (long)gp.First().SubCategoryId,

CategoryId = (long)gp.First().CategoryId,



Categoryname = gp.First().catname,

SubCategoryName = gp.First().subcatname,

SubSubCategoryId = (long)gp.First().SubSubCategoryId,



Thumbnail = gp.First().Thumbnail,

MainSmallImages = gp.First().MainSmallImages,

Productprice = (decimal)((bool)gp.First().IsSpecialOffer && gp.First().SKU_offerprice > 0 ? gp.First().SKU_offerprice : gp.First().SKU_price),



BrandName = gp.First().BrandName,

Gender = gp.First().Gender,

Color = gp.First().Color,

RRP_Price = (decimal)gp.First().SKU_rrp,



mainimagethumb2 = gp.First().mainimagethumb2,

mainimagethumb3 = gp.First().mainimagethumb3,

StockQuantity = (int)gp.First().StockQty,

IsVat = (bool)gp.First().isvat,

VatValue = (decimal)gp.First().vatvalue,

StockId = (int)gp.First().Id,



ItemCode = gp.First().ItemCode

};

result = (from t in result

where ((from s1 in db.vw_allproducts

where s1.ItemCode == t.ItemCode

&& (ptype==1?SqlMethods.Like(s1.ProductCode, "%" + serachtext + "%"):

SqlMethods.Like(s1.ProductName.Replace("?", "").Replace("<", "").Replace(">", "").Replace(" ", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace(":", "").Replace(".", "").Replace(";", "").Replace("\\", "").Replace("&", "").Replace("/", "").Replace("\"", ""), "%" + serachtext + "%"))

&& s1.IsActive == true && s1.IsDeleted == false

group s1 by new { s1.ItemCode }

into g

select g.Min(g1 => g1.ProductId)).Contains(Convert.ToInt32(t.ProductId)))

select t



).Distinct();

}