gistfile1.txt

//code sample to migrate a one to many to one to many relationship to a one to many
//patch what devices user is permitted to use
            Sql(@"INSERT INTO [dbo].[CouncilUserDevice]
           ([CouncilUserId]
           ,[DeviceId]
           ,[Enabled])
                select CouncilUserId,
                DeviceId,
				max(finalEnabled)
                from
		       (Select cu.Id as CouncilUserId, ac.Id as accesscard, d.Id as DeviceId, acd.[Enabled],
				CASE WHEN (acd.[Enabled] is null) then (
					CASE WHEN 
					count(case when (acd.[Enabled] = 0) then 1 end)
					over (PARTITION by cu.Id) > 0
					then 0
					else 1
					end) 
				else
					CASE WHEN
					sum(case when (acd.[Enabled] = 1) then 1 
                    when (acd.[Enabled] is null OR lower(ac.Description) like '%missing%' or lower(ac.Description) like '%lost%' or lower(ac.Description) like '%replaced%'or lower(ac.Description) like '%misplaced%') then 0 						
                    when (acd.[Enabled] = 0) then -1 
						end)
					over (PARTITION by cu.Id,d.Id) >=0
					then 1
					else 0
					end
				end as finalEnabled

		   from
			counciluser as cu
			full outer join
			Device as d on cu.CouncilId = d.CouncilId
			left join
			UserAccessCard as uac on cu.UserId = uac.UserId
			left join
			AccessCard as ac on uac.AccesscardId = Ac.Id and cu.CouncilId = ac.CouncilId
			left join
			AccessCardDevice as acd on acd.AccesscardId = ac.Id and acd.DeviceId = d.Id
			where cu.UsertypeId = 1
			) as query
			group by CouncilUserId, DeviceId");
            //List<CouncilUser> failList = new List<CouncilUser>();
            //using (SmartData uow = SmartDataProvider.GetUnitOfWork())
            //{
            //    List<CouncilUser> list = uow.CouncilUser.Where(cu => cu.UserType.Name == "CUSTOMER").Include(u => u.User.UserAccessCard.Select(uac => uac.AccessCard.AccessCardDevice)).ToList();
            //    foreach (CouncilUser councilUser in list)
            //    {
            //        Dictionary<long, bool> deviceDict = new Dictionary<long, bool>();
            //        bool failed = false;
            //        // iterate thru each access card. 
            //        foreach (UserAccessCard uac in councilUser.User.UserAccessCard)
            //        {
            //            //check card is not disabled at all sites if it is exclude card
            //            bool enabledCheck = uac.AccessCard.AccessCardDevice.All(acd => acd.Enabled == false);

            //            if (!enabledCheck)
            //            {
            //                //compare each card to the other cards to see if they have the same devices. If not add to the failList.

            //                foreach (AccessCardDevice acd in uac.AccessCard.AccessCardDevice)
            //                {
            //                    if (!deviceDict.ContainsKey(acd.DeviceId))
            //                    {
            //                        deviceDict.Add(acd.DeviceId, acd.Enabled);
            //                    }
            //                    else
            //                    {
            //                        if (deviceDict[acd.DeviceId] != acd.Enabled)
            //                        {
            //                            failed = true;
            //                        }
            //                    }
            //                }
            //            }
            //        }

            //        if (failed)
            //        {
            //            //query to fix failed devices goes here. 
            //            //throw new System.ArgumentException("Unhandled Exception", "device ID's were not all the same.");
            //        }
            //        else
            //        {
            //            foreach (KeyValuePair<long, bool> dev in deviceDict)
            //            {
            //                councilUser.CouncilUserDevice.Add(
            //                new CouncilUserDevice
            //                {
            //                    DeviceId = dev.Key,
            //                    Enabled = dev.Value
            //                });
            //            }
            //        }
            //    }
            //    uow.SaveChanges();

            //}