SQL Stored Procedure written in C# / LINQ

Question

Say I have a basic SQL table (@Reg A ) that stores registrations with the fields: ID (can be duplicates), pre_status and post_status.

I'm looking to tally up a bunch of the IDs for different situations. Some of the queries are very basic, like the number of IDs where pre_status in ('Enrolled', 'Waiting List'). However, other queries get more complicated because they implement sort of a tiered approach. So I may need to count distinct IDs where:

• post_event = 'Incomplete' and
• has an additional record with pre_status = 'Waiting List' or 'Enrolled' and
• does not include the IDs with a previous record of post_status = 'Complete'
• etc

I can do this using a stored procedure similar to the code below. But I'm wondering if there's a better way. If I were to take the @Reg A table and use C# / LINQ to tally the scenarios, what would a good data structure and approach be?

Here is the SQL code (the first part is just to fill the @Reg A table with sample data. the 2nd part is what I'm considering converting to C# / LINQ):

/***** Populate temporary table with random test data *****/  
declare @count int  
set @count = 1  
  
declare @RandomNum int  
declare @Pre_Status varchar(20)  
declare @Post_Status varchar(20)  
declare @rnd decimal(13, 1)  
  
  
declare @reg table (ID int, Pre_Status varchar(20), Post_Status varchar(20))  
  
  
While @count <= 2000  
Begin   
  
   set @RandomNum = Round(Rand()*1000, 0)  
     
   set @rnd = round(rand(), 1)  
   set @Pre_Status = (CASE WHEN @rnd < 0.2 THEN 'Event Full'  
   WHEN @rnd < 0.4 THEN 'Cancelled'  
              WHEN @rnd < 0.7 THEN 'Waiting List'  
              WHEN @rnd < 0.9 THEN 'Enrolled'  
              ELSE 'Prerequisite Not Met'  
   END)  
  
   set @rnd = round(rand(), 1)  
   print @rnd  
   set @Post_Status = (CASE WHEN @rnd < 0.3 THEN 'Complete'  
              WHEN @rnd < 0.4 THEN 'Incomplete'  
              WHEN @rnd < 0.5 THEN 'Prerequisite Not Met'  
   WHEN @rnd < 0.6 THEN 'No Show'  
              WHEN @rnd >= 0.7 THEN '(Not Specified)'  
              ELSE 'Other'  
   END)  
  
  
   Insert Into @reg values (@RandomNum, @Pre_Status, @Post_Status)  
   Set @count = @count + 1  
End  
  
-- select * from @reg  
  
/******* Done with Test data ***********/  
  
  
-- Create a temp table to store registrations with Event Full or Waiting List pre status  
declare @efwl table (ID int, Pre_Status varchar(20), Post_Status varchar(20))  
  
insert into @efwl   
select ID, Pre_Status, Post_Status   
from @reg   
where Pre_Status in ('Event Full', 'Waiting List')  
  
-- Create another temp table store registration with Enrolled pre status AND also had pre status = Event Full or Waiting List   
declare @enrolled table (ID int, Pre_Status varchar(20), Post_Status varchar(20))  
  
insert into @enrolled   
select ID, Pre_Status, Post_Status   
from @reg  
 where Pre_Status = 'Enrolled' and ID in (select ID from @efwl)  
  
  
select   
-- Total # of registrations either event full or waiting list  
Total = (select count(*) from @efwl),   
-- Total # of registrations equal to event full   
EventFull = (select count(*) from @efwl where Pre_Status = 'Event Full'),   
-- Total # of registrations equal to waiting list  
WaitingList = (select count(*) from @efwl where Pre_Status = 'Waiting List'),  
-- # Distinct IDs in event full waitlist table  
UniqueIndividuals = (select count(distinct(ID)) from @efwl),   
-- # distinct IDs where pre status was 'Event Full' or 'Waitlist' and post_status was never enrolled  
NotAdmitted = (select count(distinct(ID)) from @efwl where   
 ID not in (select ID from @enrolled)),  
-- # Distinct IDs in enrolled table  
Enrolled = (select count(distinct(ID)) from @enrolled),  
-- # Distinct IDs in enrolled table where Post_Status = 'Complete'  
Complete = (select count(distinct(ID)) from @enrolled where Post_Status = 'Complete'),   
-- # Distinct IDs in enrolled table where post status = 'Incomplete' and was never Complete  
Incomplete = (select count(distinct(ID)) from @enrolled where Post_Status = 'Incomplete'  
 and ID not in (select ID from @enrolled where Post_Status = 'Complete')),   
-- # distinct IDs in enrolled table where post status = 'No Show' and was never Complete or Incomplete  
NoShow = (select count(distinct(ID)) from @enrolled where Post_Status = 'No Show'  
 and ID not in (select ID from @enrolled where Post_Status in ('Complete', 'Incomplete'))),  
-- # distinct IDs in enrolled table where pre status = 'Cancelled' and post status was never Complete, Incomplete or No Show  
Cancelled = (select count(distinct(ID)) from @reg where Pre_Status = 'Cancelled'  
  and ID in (select ID from @enrolled))  

Answer 1

@Brian , you could try the following code to convert 2nd part to linq statement.

static void Main(string[] args)  
        {  
            SqlConnection connection = new SqlConnection("connstr");  
            connection.Open();  
            string sqltext = File.ReadAllText("D:\\Data.txt");       //I stored the first part to the txt file  
            SqlDataAdapter adapter = new SqlDataAdapter(sqltext, connection);  
            DataSet set = new DataSet();  
            adapter.Fill(set);  
            DataTable table = set.Tables[0];  
            var efwl = from data in table.AsEnumerable()  
                       where data.Field<string>("Pre_Status") == "Event Full" || data.Field<string>("Pre_Status") == "Waiting List"  
                       select new  
                       {  
                           ID = data.Field<object>("ID"),  
                           Pre_Status = data.Field<object>("Pre_Status"),  
                           Post_Status = data.Field<object>("Post_Status")  
  
                       };  
             
            var enrolled = from data in table.AsEnumerable()  
                           where data.Field<string>("Pre_Status") == "Enrolled"&& efwl.Select(i=>i.ID).Contains(data.Field<object>("ID"))  
                           select new  
                           {  
                               ID = data.Field<object>("ID"),  
                               Pre_Status = data.Field<object>("Pre_Status"),  
                               Post_Status = data.Field<object>("Post_Status")  
  
                           };  
            var Incomplete = enrolled.Where(i => i.Post_Status.ToString() == "Incomplete");  
            var complete= enrolled.Where(i => i.Post_Status.ToString() == "Complete");  
            var results1 = (from t1 in Incomplete  
                          where !(from t2 in complete  
                                  select t2.ID).Contains(t1.ID)  
                          select t1.ID).Distinct();  
            var Noshow = enrolled.Where(i => i.Post_Status.ToString() == "No Show");  
            var comandnotcom = enrolled.Where(i => i.Post_Status.ToString() == "Complete" || i.Post_Status.ToString() == "Incomplete");  
            var results2 = (from t1 in Noshow  
                            where !(from t2 in comandnotcom  
                                    select t2.ID).Contains(t1.ID)  
                            select t1.ID).Distinct();  
  
  
  
            var final = new Result  
            {  
                            Total = efwl.Count(),  
                            EventFull = efwl.Where(i => i.Pre_Status.ToString() == "Event Full").Count(),  
                            WaitingList = efwl.Where(i => i.Pre_Status.ToString() == "Waiting List").Count(),  
                            UniqueIndividuals = efwl.Select(i => i.ID).Distinct().Count(),  
                            NotAdmitted = efwl.Select(i => i.ID).Except(enrolled.Select(i => i.ID)).Count(),  
                            Enrolled = enrolled.Select(i => i.ID).Distinct().Count(),  
                            Complete = enrolled.Where(i => i.Post_Status.ToString() == "Complete").Select(i => i.ID).Distinct().Count(),  
                            Incomplete = results1.Count(),  
                            NoShow = results2.Count(),  
                            Cancelled = table.AsEnumerable().Where(i => i.Field<string>("Pre_Status") == "Cancelled" && enrolled.Select(m=>m.ID).Contains(i.Field<object>("ID"))).Select(i => i.Field<object>("ID")).Distinct().Count()  
             };  
  
  
public class Result  
    {  
        public int Total { get; set; }  
        public int EventFull { get; set; }  
        public int WaitingList { get; set; }  
        public int UniqueIndividuals { get; set; }  
        public int NotAdmitted { get; set; }  
        public int Enrolled { get; set; }  
        public int Complete { get; set; }  
        public int Incomplete { get; set; }  
        public int NoShow { get; set; }  
        public int Cancelled { get; set; }  
  
    }  
 }  

Result:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.