Important Linq Queries

Update with join

var InputCombipairs = from Input in InputCombinations.ObjectiveSubbannerCombinationList

                        join DBInput in DBInsertInputCombinations

                            on new { SubbannerId= Input.SubbannerId, ObjectiveId= Input.ObjectiveId } equals new { SubbannerId= DBInput.SubBannerId, ObjectiveId= DBInput.ObjectiveId }

                        select new { DBInput, Input };

            foreach (var pair in InputCombipairs)

            {

                pair.Input.ObjectiveName = pair.DBInput.Objective.Name;

                pair.Input.SubbannerName = pair.DBInput.SubBanner.Name;

            }

Sample join and group 

var UserIdsWithPersonalShifts = ActionItems.GroupBy(M=>M.UserId).Select(grp=> new

                    {

                        UserId= grp.Key,

                        MinStartTime= grp.Min(L=>L.CreationDate.Value),

                        MaxEndTime= grp.Max(L=>L.RecurrenceEndDate.Value)

                    }); 

var EligibleUserPCNotiNotNull =

                        from usr in Users.Where(x =>

x.Client.ClientDashboardConfigurations.First().RequireAutomatedPulseNoti.Value &&

                            x.PulseCheckNotiDay != null &&

                            x.PulseCheckNotiHour == 15 &&

                            x.PulseCheckNotiMinutes == 0 &&

                            x.PulseCheckNotiDay == 4 &&

x.DeviceTokenId !=null

                            ) 

join UWPS in UserIdsWithPersonalShifts on usr.Id equals UWPS.UserId

select new

                            {

                                UserId = usr.Id,

                                ClientId = usr.ClientId,

                                Name = string.Format("{0} {1}", usr.FirstName, usr.LastName),

                                DeviceTokenId = usr.DeviceTokenId,

                                LastProvideFBVisitDate = usr.LastProvideFBVisitDate,

                                PCNotiDay = usr.PulseCheckNotiDay,

                                PCNotiHour = usr.PulseCheckNotiHour,

                                PCNotiMinutes = usr.PulseCheckNotiMinutes

                            };

EligibleUserPCNotiNotNull.Dump();

Outer join (example1)

DateTime StartDate=Convert.ToDateTime("07/02/2016 18:30:00");
  var pulseCheckItems = (
                    from u in Users
                    from ai in ActionItems
                        .Where(
                            mapping =>
                                (mapping.DevAreaId.Value==85) && (mapping.UserId == u.Id) &&
                                (mapping.CreationDate.Value < StartDate ||
                                 Math.Abs((mapping.CreationDate.Value - StartDate).Days) < 7) &&
                                ((mapping.DeletionDate == null && mapping.IsDeleted.Value == false) ||
                                 mapping.DeletionDate.Value > StartDate))
                    from groups in PulseChecks
                        .Where(gruppe => gruppe.ActionItemId == ai.Id).DefaultIfEmpty()
                    from groupColor in ColorCodes
                        .Where(gruppeColor => gruppeColor.Id == groups.ColorCodeId).DefaultIfEmpty()
                    where ai.UserId == 404
                    select new
                    {
                        id = (groups != null) ? groups.Id : -1,
                        actionItemId = ai.Id != null ? ai.Id : -1,
                        actionItemTitle = ai.Title != null ? ai.Title : "",
                        colorCodeID = (groups != null && groups.ColorCodeId != null) ? groups.ColorCodeId : -1,
                        color = (groupColor != null) ? groupColor.Color : "",
                        weekStartDate = (groups != null) ? (groups.WeekStartDate) : null
                    });
pulseCheckItems =
                    pulseCheckItems.Where(
                        O => ((O.weekStartDate == null) && O.id == -1) || O.weekStartDate.Value <= StartDate);
pulseCheckItems.Dump();

Outer join (example2)

void Main()
{
var UserSpecificDevAreas = UserDevAreaMappings.Where(L => (L.UserId == 426) && (L.DevArea.IsDeleted == false)).
                    Select(K => new {DevAreaId= K.DevAreaId,Date= (DateTime?)K.DateAdded.Value}).ToList();
UserSpecificDevAreas.Dump();


var ClientDevAreas = ClientDevAreaMappings.Where(L => (L.ClientId == 47) && (L.DevArea.IsDeleted == false)).Select(O => new
                {
                    DevAreaId = O.DevAreaId,
                    DevAreaName = O.DevArea.Name,
                    IsSelected = false,
                    IconPath = "DevAreas-images" + "/" + O.DevArea.IconPath,
                    GroupId = O.DevArea.GroupId,
                    GroupName = O.DevArea.Group.Name,
                    GroupIconPath = "DevAreaGroup-images" + "/" + O.DevArea.Group.IconPath,
                    DateAdded= Convert.ToDateTime(null)
                }).ToList();
ClientDevAreas.Dump();

var ClientNUserDAMerged= (from CDA in ClientDevAreas                        
                    from USDA in UserSpecificDevAreas
                        .Where(USDA => USDA.DevAreaId == CDA.DevAreaId).DefaultIfEmpty()
select new
                    {
                         DevAreaId = CDA.DevAreaId,
                    DevAreaName = CDA.DevAreaName,
                    IsSelected = USDA!=null? true : false,
                    IconPath = CDA.IconPath,
                    GroupId = CDA.GroupId??-1,
                    GroupName = CDA.GroupName??"Others",
                    GroupIconPath = CDA.GroupIconPath,
                    DateAdded = USDA != null ? USDA.Date : null,
USDA
                    }).ToList();
ClientNUserDAMerged.Dump();



var GroupedDevAreas = ClientNUserDAMerged.GroupBy(L => new { L.GroupId, L.GroupName, L.GroupIconPath }).Select(M => new GroupedDevArea()
                {
                    GroupId = M.Key.GroupId,
                    GroupName = M.Key.GroupName,
                    IconPath = M.Key.GroupIconPath,
                    DevAreaList = M.Select(K => new DevAreaModel()
                    {
                        DevAreaId = K.DevAreaId,
                        DevAreaName = K.DevAreaName,
                        IsSelected = K.IsSelected,
                        IconPath = K.IconPath
                    }).ToList()
                });
}

  public class DevAreaModel
    {
        public long DevAreaId { get; set; }
        public string DevAreaName { get; set; }
        public bool IsSelected { get; set; }
        public string IconPath { get; set; }
        public DateTime? DateAdded { set; get; }

    }
    public class GroupedDevArea
    {
        public int? GroupId { get; set; }
        public string GroupName { get; set; }
        public string IconPath { get; set; }

        public List<DevAreaModel> DevAreaList { set; get; }

    }

// Define other methods and classes here





Enumerating AD users via LINQ utility

var domainName = Util.ReadLine("Domain Name");
var groupName = Util.ReadLine("Group Name");

using (var context = new PrincipalContext(ContextType.Domain, domainName))
{
using (var group = GroupPrincipal.FindByIdentity(context, groupName))
{
if (group == null)
{
"Group does not exist".Dump();
}
else
{

var users = group.GetMembers(true);
foreach (UserPrincipal user in users)
{
user.Dump();
}
}
}
}
-------------------------------
utility can be found at link



custom distinct by in linq

public static IEnumerable<TSource> DistinctBy<TSource, TKey>

   (this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)

        {

            HashSet<TKey> seenKeys = new HashSet<TKey>();

            foreach (TSource element in source)

            {

                if (seenKeys.Add(keySelector(element)))

                {

                    yield return element;

                }

            }

        }