CodeFlunkie

Referenced from MSDN: https://msdn.microsoft.com/en-us/library/bb397947.aspx Query syntax: IEnumerable<int> numQuery1 =               from num in numbers             where num % 2 == 0             orderby num             select num; Method syntax:         IEnumerable<int> numQuery2 = numbers.Where(num => num % 2 == 0).OrderBy(n => n)

 var urlList = new List<DisplayAssets>();    var assetsToExclude = (from a in db.DisplayAssets                 join h in db.Hardware_Assets on a.id equals h.displayId                 where h.assetId == hardwareid                 select a.id); var allAssets = db.DisplayAssets; var urls = allAssets.Where(x => !assetsToExclude.Contains(x.id));         urlList = urls.Select(a => new DisplayAssets()         {             locaterAddress  = a.locaterAddress,             assetType = a.assetType,             id = a.id         })         .ToList();

      public IList GetTeamcallSummary()        {            int currentmonth = DateTime.Today.Month;            int lastmonth = DateTime.Today.AddMonths(-3).Month;                    var calldata = (from c in db.calldetail_viw                where c.ConnectedDate.Value.Month >= lastmonth                      && c.ConnectedDate.Value.Month <= currentmonth                      && c.LocalName == "myaccountrep"                            group c by new { c.AssignedWorkGroup, month = c.ConnectedDate.Value.Month }                into g       ...

Needed to return a single value and cast it as an int instead of an int?     public int getFranchiseePOS(string AccountCode)         {             var qry = (from s in MyEntities.Franchisees                        join p in MyEntities.POS on s.POSID equals p.POSID into XX                        from subgroup in XX.DefaultIfEmpty()                        where s.AccountCode == AccountCode                        select s.POSID==null ? 0 :(int) s.POSID                       ).FirstOrDefault();             return qry;         }

I was looking for a good example of a Join but the examples I found were simple and did not include the other elements such as the where statement and multiple tables. So here is an example that works that includes those elements. I have eliminated some of the fields if you are wondering why I have so many joins that I didn't appear to use. The following query has multiple joins, one of the joins is joining on two columns with a defaultifempty applied - essentially making it a left join. One of the fields is an integer but I wanted to switch out a string instead - hopefully this will help someone who is looking for the answer for one of these linq constructors. MyEntities tfs = new MyEntities(); var rptCon = (from e in tfs.Company_Zips join b in tfs.Franchisees on e.AccountCode equals b.AccountCode join c in tfs.Account_StoreExtensions on new{ e.Zip_StoreExtensionID,e.Accountcode} equals new{ c.StoreExtensionID,c.AccountCode} into ex join p in tfs.Postal_Code_Territor...

So you can't do this: where e.myfield=myEnum.myvalue or this where e.myfield=1 when myfield is a nullable integer In that situation you need to use .Equals Like this: where e.myfield.Value.Equals(myEnum.myvalue) Voila!

var numberText = ( from n in numbers where n > 0 select new { Number = n , Text = ( n == 1 ? "One" : n == 2 ? "Two" : n == 3 ? "Three" : "Unknown" ) } );

Example of a single computed value from database, not a declared entity type  private decimal getTuneupAmount(DateTime dateCompleted,int classid, int  id)         {        string sql = @"SELECT  (count(ow.orderworkid) * (select tuneupamount from mastertable)) as TuneupAmount";                         sql += " from tbl_o o ";                         sql += " join tbl_work ow on o.orderid=ow.orderid ";                         sql += " join _setup s on ow.taskcodenumber=s.TaskCodeNumber and s.typeid =2 ";                         sql += " WHERE   ow.soldbyid =" + id + "  and ow.solddate ='" + dateCompleted+ "' and o.classcodeid=" + classid + " ";       ...

var terms = from t in HRSystemDB . Terminations select new { Month = t . TerminationDate . Month , Year = term1 . TerminationDate . Year , IsHire = false }; var hires = from emp in HRSystemDB . Persons . OfType < Employee >() select new { Month = emp . HireDate . Month , Year = emp . HireDate . Year IsHire = true }; // Now we can merge the two inputs into one var summary = terms . Concat ( hires ); // And group the data using month or year var res = from s in summary group s by new { s . Year , s . Month } into g select new { Period = g . Key , Hires = g . Count ( info => info . IsHire ), Terminations = g . Count ( info => ! info . IsHire ) }

Using a nullable Boolean (bool?) datatype in LINQ can cause some issues You can return those values by using the nullable operator as shown below   Transfer = e.Transfer ?? false This will return the value from e.Transfer and if the value is null will return False

Left joins don't come natural to LINQ but the solution is not that complicated. Below is my example I needed to pass in 3 fields for my where clause and I was getting back the LoginName field from the USERS table - unfortunately sometimes this field was empty and my join would not work - in the SQL we know and love this is easily accomplished with a left join (take all the fields from the first table or the table on the left and any that match in the second table) Below you can see how this works. The 'subgroup' referenced below is just an alias, there is no table named subroup - almost acting like a temptable.  var mygroupitems = (from e in MyEntities.Terr_History                             join p in MyEntities.Users on e.Approver_User_Id  equals p.Users_Id into XX                             from subgroup in XX.DefaultIf...

Spent so much time on this error, examining my models, data structure etc. What it boiled down to was the sortability of the IEnumerable object on the page vs a sortable list. All you need to do most of the time is to recast it in your controller like this:  var retdata = bl.GetPidHistory(pid);  var itemstoreturn = retdata.ToList();  return View(itemstoreturn);  This is applicable when your view has a model like this: @model IEnumerable

What a challenge I was facing trying to figure out how to solve this problem in LINQ.. but i did it .. and am sharing. SQL query: Select Distinct  o.ConceptCode, u.firstname, u. lastname , (select count(*)  from callLead  where calldate ='2014-09-02'  and conceptcode=O.conceptcode and userid=O.userid) 'DailyTotal' ,(select count(*) from callLead where calldate between '2014-09-01'  and '2014-09-05' and conceptcode=O.conceptcode and userid=O.userid) 'WTD' ,(select count(*) from callLead where datepart(m,calldate)=9 and conceptcode=O.conceptcode and userid=O.userid) 'MTD' ,(select count(*) from callLead where datepart(yyyy,calldate)=2014 and conceptcode=O.conceptcode and userid=O.userid) 'YTD' from callLead O join users u on o.userid=u.userid The LINQ Conversion: The LINQ version is much prettier actually  var rptSummary = (from e in SalesEntities.CallLeads                         ...

I have a dropdown list where the first selection is to return ALL Records and the value is 0 otherwise they make a selection and it returns the equivalent integer. Here is how I worked it out in my Linq query The dropdown is passing into a variable called ClassCodeID  && (ClassCodeID.Equals(0) || s.ClassCodeID >= ClassCodeID || s.ClassCodeID == ClassCodeID) And that seemed to work... however when returning values it did not roll up the values as I expected since classcodeID was not in the groupby.. it only returned a record for a specific code but the values from all codes were rolled into the returned data.  I added an additional where statment and this seemed to have fixed the issue . where (ClassCodeID.Equals(0) || s.ClassCodeID == ClassCodeID

So the first request was to use  two date fields one optional dropdown value an optional textbox  value  Below is the first query I created which satisfied this request.   But scope creep raised its ugly head and the requirements were changed..  Now I need to provide results for either an 'empty' textbox or any entry such as the first couple of letter with  results similar to a "Like" statement - the second example shows how I changed to accommodate.  var dbRecords = (from c in context.tbl_mydata                             where (c.DateRan >= startdate && c.DateRan <= enddate)                                  && (string.IsNullOrEmpty(mnum) || string.Compare(c.MNumber, mnum, true) == 0)                     ...

 I needed to get essentially a summary by case statement for a given date.  As you can see I have defined the MAX date into a variable.  This example also shows using a substring of the first character in a LET statement  DateTime? maxDate =                 (from a in context.tbl_tslog                 orderby a.CurrentTime                 select (DateTime?) a.CurrentTime ).Max(); var dbRecords = (from c in context.tbl_testlog                            where (c.CurrentTime >=maxDate)                                         let range = (  c.Usr.ToUpper().Substring(0,1) == "B" ? "US" :                     ...

Having little to no experience with Linq this one was rather tough. .  I googled Conditional Data Entity but found nothing as slick as this solution a friend sent me. So the problem was this ... I had some textboxes, date fields etc for report filters but the textbox was OPTIONAL.  I wanted to manage the condition in the query.   So here is what we came up with: var dbRecords = (from c in context.myTable where (string.IsNullOrEmpty(bnum) || string.Compare(c.BNumber, bnum, true) == 0 )  && (c.DateRan >= startdate && c.DateRan <= enddate) And the way this works  - if bnum has a value then we will select where c.BNumber = bnum otherwise we don't use it in the where statement at all.  Really made life easy!