Powered by Passion

In to Linq query, we can use dynamic query: public List<Insplan> GetInsplanLookupList(string planName = null, string binNo = null, string procCtrl = null) { var query = (from i in _db.insplan orderby i.cinsplanname ascending select i).AsQueryable(); if (!string.IsNullOrWhiteSpace(planName)) query = query.Where(x => x.cinsplanname.StartsWith(planName.Trim())); if (!string.IsNullOrWhiteSpace(binNo)) query = query.Where(x => x.cbinno.StartsWith(binNo.Trim())); if (!string.IsNullOrWhiteSpace(procCtrl)) query = query.Where(x => x.cprocctrlno.StartsWith(procCtrl.Trim())); return (from q in query join ic in _db.inscomp on q.inscompid_FK equals ic.inscompid_PK into ic_joined from ic in ic_joined.DefaultIfEmpty() join cl in _db.clinic on q.clinicid_FK equals cl.clinicid_PK into cl_joined from cl in cl_joined....

You resolve your auto increment  jumping from this http://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database you can also resolve the issue into another way if you are using EDMX. Before inserting data into table get the max id value from your table. int maxAge = context.Persons.Max(p => p.Age); Now add your increment number  maxAge+1   Map the id value with your table. NB: You table Identity specification : Is Identity Will be no.

See the sample: var customer = (from cus in _billingCommonservice.BillingUnit.CustomerRepository.GetAll() join man in _billingCommonservice.BillingUnit.FunctionRepository.ManagersCustomerValue() on cus.CustomerID equals man.CustomerID // start left join into a from b in a.DefaultIfEmpty(new DJBL_uspGetAllManagerCustomer_Result() ) select new { cus.MobileNo1,b.ActiveStatus });

we can easily find out the sum from a list of collection. if the list is used in data grid or any other control as a data source we can easily find out the sum of any item field from the list using LINQ. var totalQty = ViewData.Requisition.DC_RequisitionItemList.Sum(item => item.ItemTotalValue); ViewData.TotalQty = totalQty; Thanks.

We can use LINQ update using more than one where. // Upadate PR_RECIVEDINVOICEs status var users = from u in dataContextObj.PR_RECIVEDINVOICEs where u.RecivedInvoice_PublisherInvoice == receiveBook.InvoiceNo && u.Status=="Order" select u; users.ToList().ForEach(u => u.Status = "Book Receive"); dataContextObj.SubmitChanges();

Here i just write down the direct code for LINQ date range search 1: internal List<Book> GetStockInRecord(Book bookObj) 2: { 3: _dataContextObj = new KARIM_INT_SECURITY_DataClassesDataContext(); 4: List<Book> bookLsit = new List<Book>(); 5: foreach (var p in (from j in _dataContextObj.Stock_Ins 6: where j.StockDate >= bookObj.StockFromDate && j.StockDate <= bookObj.StockToDate 7: select j).Distinct()) 8: { 9: Book aBook = new Book(); 10: aBook.BookId = (int) p.BookId; 11: aBook.BookIsbnNo = p.AD_BOOK.Book_BookIsbnNo; 12: aBook.BookTitle = p.AD_BOOK.Book_BookTitle; 13: aBook.BookAuthorName = p.AD_BOOK.Book_BookAuthor; 14: aBook.BookBinding = p.AD_BOOK.Book_BookBinding; 15: aBook.BookCategoryName = p.AD_BOOK.Book_BookCategory; 16: aBook.BookCurrencyName = p.AD_BOOK.Book_Currency; 17: ...

In Your application if you have more than one DBML file then each DBML file will contain app.config for each DBML file. consider the WPF application it will be not possible for you to change the client app.config connection string on deployment. when your application will be ready for deployment take a copy of your solution from your source control. bcoz now you are going to perform many change on your app.config. 1. if you have more than one project then you have change the all project output type as a class library except the start up project.    right click on the project > properties > change the output as class library 2. Click on setting > right click on the connection string click remove string. if you have more than one then remove all as same. 3. if you have app.xaml . right click and exclude it from project. 4. open your DBML file > right click > properties > expand the connection > select application string as false. ...

LINQ is become one of most popular. But Based on our business logic we need to Use Transaction in LINQ. LINQ Transaction is much more smart. You do not need to rollback. If it fail to complete any query it automatically rollback. To use transaction You have to add a .NET dll name  "System.Transaction" in reference of your application. . Now in your DataAccess Layer you can use like public void UpdateSaleDetailsTable(ESalesReturn aSalesReturn) { using(System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required)) { UpdateSaleDetailsTableData(aSalesReturn); UpdateSaleTable(aSalesReturn); UpdateCurrentProductTable(aSalesReturn); InsertIntoSalesReturnTable(aSalesReturn); scope.Complete(); } }

There is no doubt "LINQ" is much more smart that other. In our development we face complex transaction or logical query for our application. Hare is some common LINQ opration. It is important that your all table should be present in dbml file.  Hare PRODUCT_NAME is the table name, In which i will perform All LINQ operation.  Product is the class name which will contain data.  table should contain a Primary Key. LINQ INSERT: public void SaveNewProductName(Product aProduct) { var newProductName = new PRODUCT_NAME { NAME = aProduct.ProductName }; dataContexObj.PRODUCT_NAMEs.InsertOnSubmit(newProductName); dataContexObj.SubmitChanges(); } LINQ DOESEXIST: public bool DoesExistProductName(Product aProduct) { return (dataContexObj.PRODUCT_NAMEs.Any(o => o.NAME.Contains(aProduct.ProductName))); } LINQ UPDATE: pu...

The SQL BETWEEN operator we can use in C# code. Before your query you have to use System.Globalization.CultureInfo culInfo = new System.Globalization.CultureInfo("en-US");   Consider the Example: internal List<ECalendarSetup> GetCalenderInfoOnSelectedDate(ECalendarSetup calendarSetup) { ieclHrmDataContext = new IECL_HRMDataContext(); List<ECalendarSetup> calendarSetupsList = new List<ECalendarSetup>(); var query = from j in ieclHrmDataContext.HR_CALENDAR_INFOs where j.CAL_DAY_DATE >= calendarSetup.FromDate && j.CAL_DAY_DATE <= calendarSetup.ToDate select j; foreach (var calendarInfo in query) { ECalendarSetup eCalendarSetup = new ECalendarSetup(); eCalendarSetup.Date = (DateTime) calendarInfo.CAL_DAY_DATE; eCalendarSetup.DayStatus = calendarInfo.CAL_DAY_STATUS; ...

Spliting string in C# is one of the most interesting part. you can also perform your business logic operation on spliting part. Hare is a list of serial no comes from database, i split the Serial no then just increment id no +1 on maximum serial no, and return to UI. i also check hare is the serial no is in current year. internal string GetNewSerialNo() { string refence = ""; DateTime dt = DateTime.Now; List<RFQ> _listAllRef =new List<RFQ>(); foreach (var objrfq in rfqDalObj.GetNewSerialNo()) { string[] splitedRef = (objrfq.SerialNO).Split('-'); if(DateTime.Now.Year.ToString()==splitedRef[1]) { _listAllRef.Add(objrfq); } } List<int> rfqSerials = new List<int>(); if (_listAllRef.Count > 0) { foreach (var obj in _listAllRef) { string[] splitedRef = (obj.Serial...