Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

LINQ statement using PARTITION BY

Posted on 2010-09-07
4
Medium Priority
?
3,727 Views
Last Modified: 2013-11-11
Hi Experts,
I have the sql query below which returns locate_ids adding a letter at the end if the locate_id shows up multiple times (ie 133A,133B,133C). I need to generate this query using LINQ (C#).

Does LINQ have an equivalent to the PARTITION BY statement or can this be accomplished some other way?
select l.locate_id ||
	case when row_number() over (partition by locate_id order by locate_id) > 0 then
 	 chr(64 + row_number() over (partition by locate_id order by locate_id))
	end locate_id
from locate l

inner join locate_schedule ls
on l.locate_schedule_id = ls.locate_schedule_id

inner join locate_schedule ls2
on ls.locate_schedule_id = ls2.orig_locate_schedule_id or ls.orig_locate_schedule_id = null

Open in new window

0
Comment
Question by:UKSystemSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 4

Expert Comment

by:Maverick543
ID: 33624299
There is no exact equivalent, but with the 'let' operation you can achieve the same.
This article shows some examples. Search for 'partition by' on the page (around halfway down on the page. http://smehrozalam.wordpress.com/tag/linq/
Kind Regards
Marco

Such scenarios are beautifully handled by TSQL’s Ranking_functions over partition by clauses. Here’s a typical example that uses Row_Number() function to assign a rank to each row per Person.

01 ;With PersonOrderWithRank  
02 as 
03 (  
04     Select *, Rnk = ROW_NUMBER() over (partition by PersonID order by OrderDate desc)  
05     from PersonOrders  
06 )  
07    
08 Select *  
09 from PersonOrderWithRank  
10 where Rnk=1 

In LINQ, similar result can be achieved by using the let keyword. Here’s an example:

1 from p in PersonOrders  
2 //where conditions or joins with other tables to be included here  
3 group p by p.PersonID into grp  
4 let MaxOrderDatePerPerson = grp.Max ( g=>g.OrderDate )  
5    
6 from p in grp  
7 where p.OrderDate == MaxOrderDatePerPerson  
8 select p 

Open in new window

0
 

Author Comment

by:UKSystemSupport
ID: 33630610
Hi Marco,
Thanks for the response.
The article is pretty helpful, however not quite what I am looking for. Based on that example I am able to get the max, min, or even the count for a given group of records returned. I also found a similar post here:
http://efreedom.com/Question/1-3100530/Generate-SQL-COUNT-PARTITION-ColumnName-LINQ-SQL

What I would need is to return some kind of index (the letter) incrementing within each group. Ie: if ID 133 shows up 3 times then have it show up as 133A, 133B, 133B.
0
 
LVL 1

Accepted Solution

by:
yamir2k earned 2000 total points
ID: 33636177
Here is the solution for your task. Yoy need to group your data by locate_id and then use Select extension method that takes a lambda expression with the second input parameter (int).
//Exemplery class
public class Location
{
  public int LocateID { get; set; }
  //You may add other properties if needed
}

...

//It's supposed that you get this collection from DB (using LINQ //to SQL or w/e)
List<Location> locations = new List<Location> {
   ...
};

//Grouping locations by LocateID property
//SubLocations is an indexed collection of location in a group
var groups = from l in locations
             group l by l.LocateID int g
             select new {g.Key,
                     SubLocations = g.Select((loc, i) => 
                             new {Location = loc, Index = i }};

//Obtaining the resulting set
var results = from g in groups
             let groupCount = g.SubLocations.Count()
             from l in g.SubLocations
             select new {l.Location,
                     LocateCode = count > 1 ? g.Key.ToString() :
                        g.Key.ToString() + (char)(l.Index + 'A')};

//An example on using results
foreach (var entry in results)
{
  Console.WriteLine("{0}", entry.LocateCode);
}

Open in new window

0
 

Author Closing Comment

by:UKSystemSupport
ID: 33640257
Thanks yamir2k!
This is what I was looking for.

Just to note that the Select extension method will only work with Lists.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question