For the purpose of setting up various unit and functional tests in a C# project, I needed to grab some random rows from a database table to analyze. However, rather than just any random batch of records, I specifically wanted to grab a single, random member of each and every unique type.
I created some code which does this handily in TSQL, but for my C# project I needed to do the same using entity framework.
The table consists of an auto-incrementing primary key column, a uniqueidentifier column, and a name column, and the data are a random number of several different types of flowers. Here's how to create a table in SQL with some test data.
-- Reduce clutter in the Results tab as records get inserted or updated.
SET NOCOUNT ON
GO
-- Variables.
DECLARE
@count int,
@max int,
@innercount int,
@innermax int,
@name varchar(100)
-- Table variable definition.
DECLARE @Flowers TABLE
(
ID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
Guid uniqueidentifier,
Name varchar(100)
)
-- Start by inserting several flower names.
INSERT INTO @Flowers (Name)
SELECT 'Black Lotus'
UNION ALL
SELECT 'Dandelion'
UNION ALL
SELECT 'Rose'
UNION ALL
SELECT 'Tulip'
UNION ALL
SELECT 'Orchid'
-- Set up a WHILE loop based on the number of flowers named in the table.
SELECT @count = 1, @max = COUNT(Name)
FROM @Flowers
-- Iterate through the list of flower names.
WHILE @count <= @max
BEGIN
SELECT @name = Name
FROM @Flowers
WHERE ID = @count
-- Set up another WHILE loop with a minimum value of 1, and a maximum
-- value randomly picked from between 0 and 99, made effectively 1 to
-- 100 by adding +1 to the random value.
SET @innercount = 1
SET @innermax = ( ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 99 ) + 1
WHILE @innercount <= @innermax
BEGIN
-- Insert a new record for the current flower Name along with a
-- randomly chosen GUID value.
INSERT INTO @Flowers (Guid, Name)
SELECT NEWID(), @name
SET @innercount = @innercount + 1
END
-- Just to indicate how many of each type got inserted.
PRINT 'Inserted ' + CAST(@innercount AS varchar(4)) + ' ' + @name
SET @count = @count + 1
END
-- Remove the placeholders with no guid assigned.
DELETE
FROM @Flowers
WHERE Guid IS NULL
Now, I can use the following query in TSQL to grab a single unique member of each type of flower:
-- Return a single member picked at random of each group of flowers.
SELECT DISTINCT
Name,
(SELECT TOP 1 GUID FROM @Flowers WHERE Name = f.Name ORDER BY NEWID())
FROM @Flowers f
GROUP BY Name
So now how do we reproduce the above results using entity framework? Like so:
public void Main()
{
var result = new List();
// Declare a new instance of Random.
var rand = new Random();
// Query to determine how many unique instances of each flower name live in the table.
var flowerCounts = Flowers
.GroupBy(f => f.Name)
.Select(f => new { name = f.Key, count = f.Count() });
// Broad query to grab a list of flower names and unique identifiers.
var flowers = Flowers
.Select(f => new FlowerName { name = f.Name, uniqueIdentifier = f.Guid.ToString() });
// Iterate through the list of flower names and their respective counts.
foreach (var flowerCount in flowerCounts)
{
// Here we pick a random number between 1 and the number of flowers of the current name.
var skip = rand.Next(1, flowerCount.count);
// Now, we refine the broad query to group by flowers of a particular name, skipping
// according to the randomly determined skip interval, and then grabbing the first.
// Then we just order by name.
var uniqueSingleton = flowers
.Where(f => f.name == flowerCount.name)
.GroupBy(a => a.name, (key, s) => s.Skip(skip).First())
.OrderBy(f => f.name);
foreach (var combo in uniqueSingleton)
{
Console.WriteLine(string.Format("{0}: {1}", combo.name, combo.uniqueIdentifier)
+ Environment.NewLine);
}
}
}
public class FlowerName
{
public string name{ get; set; }
public string uniqueIdentifier { get; set; }
}
Now as with the SQL example above, executing the above results in a collection of distinct flower names along with a unique row identifier for each:
