Posts

Showing posts from March, 2014

Another Conditional Linq for int values

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

Linq Conditional Selection - handling 'like' in Linq

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)                                  && (string.IsNullOrEmpty(concept) || string.Compare(c.Concept, concept, true) == 0)                                  && (!z || c.MNumber.Length <= 5)                                  && (!

Linq - Data Entities - Records by Max Date with CASE

Image
 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" :                             c.Usr.ToUpper().Substring(0, 1) == "G" ? "CA" :                             c.Usr.ToUpper().Substring(0, 1) == "U" ? "UK" : "Other"                                               )                                  group c by range into g  select new { Group = g.Key, Users = g.Count(), Latest

Conditional Linq value and a beautiful solution

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!

Grouping with Data Entities

I am really loving the data entity in my new projects but have come across a couple of issues already. Tonight I learned about using a field that is not a part of your database and not in the entity but is a consolidation field from a group.  Once I found some good samples it worked incredibly well and I had a little summary grid on the page in just a couple of minutes.  So here is what I did  using (myEntityName context = new myEntityName())     { //First I declared a couple of variables because I was looking at data between specific dates          DateTime startdate = DateTime.Parse("2014-02-01");             DateTime enddate = DateTime.Parse("2014-02-01");          var dbRecords = (from c in context.myTableName                           where (c.MemberStartDate >= startdate && c.MemberStartDate <= enddate)                           group c by c.Concept into g   //So you notice I used a g for the group by set of data //The g.key part th