CODEWITHSUNDEEP
c#linqlinq-to-sql
user1282609
user1282609

Reputation: 575

getting records from list based on category

Query:

List<vwBookTitleKitsSummary> theseKits = (from k in _dataContext.vwBookTitleKitsSummaries
                                                      where k.DateKitEnds > DateTime.Now && k.DateKitStarts <= DateTime.Now && k.IsDeleted == false 
                                                      orderby k.KitOrder, k.KitCode descending
                                                      select k).ToList();

thesekits got 6 results like below (the highlighted ones):

enter image description here

I want a query that returns one item from each category for example I want the records K1503, T1503 i.e.

I want first record that start with K and first record that start with T

I tried like following:

List<vwBookTitleKitsSummary> theseKits = (from k in _dataContext.vwBookTitleKitsSummaries
                                                      where k.DateKitEnds > DateTime.Now && k.DateKitStarts <= DateTime.Now && k.IsDeleted == false && (k.KitCode.StartsWith("K"))
                                                      orderby k.KitOrder, k.KitCode descending
                                                      select k).Take(1).ToList();

List<vwBookTitleKitsSummary> qry = (from k in _dataContext.vwBookTitleKitsSummaries
                           where k.DateKitEnds > DateTime.Now && k.DateKitStarts <= DateTime.Now && k.IsDeleted == false && (k.KitCode.StartsWith("T"))
                           orderby k.KitOrder, k.KitCode descending
                           select k).Take(1).ToList();
theseKits.AddRange(qry);

This will return records with KitCode - K1503, T1503

Instead of writing two queries and using AddRange can that be done in one query, any help please.

Upvotes: 1

Views: 58

Answers (2)

Raja Nadar
Raja Nadar

Reputation: 9499

This should give you 1 record per category, for all starting letters in your category.

List<vwBookTitleKitsSummary> firstCategoryTheseKits = _dataContext
     .vwBookTitleKitsSummaries.Where(k => k.DateKitEnds > DateTime.Now 
      && k.DateKitStarts <= DateTime.Now && k.IsDeleted == false).OrderBy(k => k.KitOrder)
     .ThenByDescending(k => k.KitCode).GroupBy(k => k.KitCode[0]).Select(grps => grps.First)
     .ToList();

If you want only for T or K, you can add a where condition as follows:

List<vwBookTitleKitsSummary> firstCategoryTheseKits = _dataContext
     .vwBookTitleKitsSummaries.Where(k => k.DateKitEnds > DateTime.Now 
     && k.DateKitStarts <= DateTime.Now && k.IsDeleted == false 
     && (k.KitCode[0] == 'T' || k.KitCode[0] == 'K')).OrderBy(k => k.KitOrder)
     .ThenByDescending(k => k.KitCode).GroupBy(k => k.KitCode[0])
     .Select(grps => grps.First).ToList();

Sorry my query is in concise notation, instead of your query notation.

Upvotes: 2

Gert Arnold
Gert Arnold

Reputation: 109118

You can group by the first character and take the first item of each group:

from k in _dataContext.vwBookTitleKitsSummaries
where k.DateKitEnds > DateTime.Now && k.DateKitStarts <= DateTime.Now
   && k.IsDeleted == false 
orderby k.KitOrder, k.KitCode descending
group k by k.KitCode.SubString(0,1) into grp
select new { grp.Key, k = grp.FirstOrDefault() }

Or just select grp.FirstOrDefault() if you like.

Upvotes: 1

Related Questions