Carpool Project: Part #14

I had a new requirement pop up today. Seems that some users only want to see the carpools/practices that their kids are in – versus the entire set. Using my Alot based design, I wanted a function where the userName came in and the week’s carpools came out. I added the needed table (DriverSwimmer) to make the association between the user and swimmers and then though about my LINQ. I first coded up this solution:

1 public static List<Carpool> GetCarpools(DateTime startDate, DateTime endDate, string userName) 2 { 3 List<Carpool> carpools = new List<Carpool>(); 4 5 using (CarpoolEntities carpoolEntity = new CarpoolEntities()) 6 { 7 var carpoolQuery = (from carpool in carpoolEntity.Carpool_Carpool 8 .Include("Carpool_Driver") 9 .Include("Carpool_Driver.Carpool_DriverSwimmer") 10 .Include("Carpool_CarpoolSwimmer") 11 .Include("Carpool_CarpoolSwimmer.Carpool_Swimmer") 12 .Include("Carpool_CarpoolPractice") 13 .Include("Carpool_CarpoolPractice.Carpool_Practice") 14 .Include("Carpool_CarpoolPractice.Carpool_Practice.Carpool_SwimGroupPractice") 15 .Include("Carpool_CarpoolPractice.Carpool_Practice.Carpool_SwimGroupPractice.Carpool_SwimGroup") 16 .Include("Carpool_CarpoolPractice.Carpool_Practice.Carpool_SwimGroupPractice.Carpool_SwimGroup.Carpool_SwimTeam") 17 where carpool.CarPoolDate >= startDate 18 && carpool.CarPoolDate <= endDate 19 && carpool.Carpool_Driver.UserName == userName 20 orderby carpool.CarPoolDate 21 select carpool); 22 foreach (Carpool_Carpool carpool_carpool in carpoolQuery) 23 { 24 carpools.Add(MapCarpool(carpool_carpool)); 25 } 26 string s = EntityExtensionMethods.CustomExtensions.ToTraceString(carpoolEntity); 27 28 } 29 return carpools; 30 31 32 } 33  

The problem is that nothing is coming back. When I looked at the SQL being generated, it was null. I wonder if there are some phrases that are so complex that EF can’t figure it out. I thought of a great product/tool – you put in the sql you want and out comes the Linq. In any event, I decided to take a shortcut – I would get a large result set back and just parse it down for the user info.

1 var query = (from s in swimmers 2 select s.SwimGroup).Distinct(); 3 List<SwimGroup> swimGroups = query.ToList<SwimGroup>(); 4  

I combined to:

1 List<SwimGroup> swimGroups = ((from s in swimmers 2 select s.SwimGroup).Distinct()).ToList<SwimGroup>(); 3  

And then I wrote this:

1 var x = (from c in carpools 2 select c.Practices).ToList<Practice>(); 3  

and got the following error:

1 'System.Collections.Generic.IEnumerable<>' does not contain a definition for 'ToList' and the best extension method overload 'System.Linq.ParallelEnumerable.ToList<TSource>(System.Linq.ParallelQuery<TSource>)' has some invalid arguments

Apparently, ToList<>() does not have a ToList<>() method. I changed it to:

1 List<Practice> practices = (List<Practice>)(from c in carpools 2 select c.Practices); 3  

And the compiler was happy

I then completed my 1st attempt: note that I have 2 sets of swimgroups – those associated with the drivers (via their swimmers) and those associated with the practices (via their carpool):

1 public static List<Carpool> GetCarpools(DateTime startDate, DateTime endDate, string userName) 2 { 3 List<Swimmer> swimmers = DriverFactory.GetDriver(userName).Swimmers; 4 List<SwimGroup> driverSwimGroups = ((from s in swimmers 5 select s.SwimGroup).Distinct()).ToList<SwimGroup>(); 6 7 8 List<Carpool> carpools = GetCarpools(startDate, endDate); 9 List<Practice> practices = (List<Practice>)(from c in carpools 10 select c.Practices); 11 List<SwimGroup> practiceSwimGroups = (List<SwimGroup>)((from p in practices 12 select p.SwimGroups).Distinct()); 13 14 return null; 15 16 } 17  

 

I then needed to compare the swimGroup list – if they are in both, then the carpool should be returned like this:

1 List<SwimGroup> commonSwimGroups = (List<SwimGroup>)practiceSwimGroups.Union(driverSwimGroups);

However, I didn’t use this. I kludged up a 5-deep for..each nest of spaghetti code:

1 public static List<Carpool> GetCarpools(DateTime startDate, DateTime endDate, string userName) 2 { 3 List<Swimmer> swimmers = DriverFactory.GetDriver(userName).Swimmers; 4 List<SwimGroup> driverSwimGroups = ((from s in swimmers 5 select s.SwimGroup).Distinct()).ToList<SwimGroup>(); 6 7 List<Carpool> carpools = GetCarpools(startDate, endDate); 8 9 10 List<Carpool> returnValue = new List<Carpool>(); 11 foreach(Carpool carpool in carpools) 12 { 13 foreach (Practice practice in carpool.Practices) 14 { 15 foreach (SwimGroup practiceSwimGroup in practice.SwimGroups) 16 { 17 foreach (SwimGroup driverSwimGroup in driverSwimGroups) 18 { 19 if (practiceSwimGroup == driverSwimGroup) 20 { 21 returnValue.Add(carpool); 22 } 23 } 24 } 25 } 26 } 27 28 return returnValue; 29  

I have to believe there is a better way with some LINQ. I will investigate – until then, at least it is working…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: