How to query linq based on combinations of IDs

Question

UserNotificationTypeDeliveryChoice - already taken combinations

Already taken combinations (1,2),(3,2),(4,1),(4,2)

NotificationGroupUserType - possible combinations

Based on this table, free combinations are: (1,1),(5,2)

Task: I need combinations from NotificationGroupUserType table which does not contain already taken combination

An example:

List<int> notificationGroupIds = selectedNotificationsByUser
    .Select(m => (int)m.NotificationGroupId)
    .ToList();
List<int> deliveryTypeIds = selectedNotificationsByUser
    .Select(m => (int)m.DeliveryTypeId)
    .ToList();

var result = _dbContext.NotificationGroupUserType
    .Include(m => m.NotificationGroup)
    .Include(m => m.DeliveryType)
    .Where(m => m.UserTypeId == (int)userType
        && !notificationGroupIds.Contains((int)m.NotificationGroupId)
        || !deliveryTypeIds.Contains((int)m.DeliveryTypeId)
    )
    .Select(m => new NotificationGroupUserType()
    {
        DeliveryType = m.DeliveryType,
        NotificationGroup = m.NotificationGroup
    })
    .ToList();

It returns just (5,2), because its excludes (1,1) because of NotificationGroupId exists already, but not as a combination with 1!

Answer 1

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
         static void Main(string[] args)
         {
             DataTable dt = new DataTable();
             dt.Columns.Add("NotificationGroupId", typeof(int));
             dt.Columns.Add("DeliveryTypeId", typeof(int));

             dt.Rows.Add(new object[] { 1, 1 });
             dt.Rows.Add(new object[] { 3, 2 });
             dt.Rows.Add(new object[] { 4, 1 });
             dt.Rows.Add(new object[] { 4, 2 });
             dt.Rows.Add(new object[] { 5, 2 });
             dt.Rows.Add(new object[] { 1, 1 });
             dt.Rows.Add(new object[] { 3, 2 });
             dt.Rows.Add(new object[] { 4, 1 });
             dt.Rows.Add(new object[] { 4, 2 });
             dt.Rows.Add(new object[] { 5, 2 });

             List <CompareID> ids = dt.AsEnumerable()
                 .Select(x => new CompareID() { NotificationGroupId = x.Field<int>("NotificationGroupId"), DeliveryTypeId = x.Field<int>("DeliveryTypeId") })
                 .Distinct()
                 .ToList();

             Boolean found = ids.Contains(new CompareID() { NotificationGroupId = 5, DeliveryTypeId = 5 });

         }
    }
    public class CompareID : IEquatable <CompareID>
    {
        public int NotificationGroupId { get; set; }
        public int DeliveryTypeId { get; set; }

        public bool Equals(CompareID other)
        {
            if ((this.NotificationGroupId == other.NotificationGroupId) && (this.DeliveryTypeId == other.DeliveryTypeId))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public override bool Equals(Object obj)
        {
            return this.Equals((CompareID)obj);
        }

        public override int GetHashCode()
        {
            return (NotificationGroupId.ToString() + "^" + DeliveryTypeId.ToString()).GetHashCode();
        }
    }

}

Answer 2

This is because you test both ids independently instead of in pairs. Try to use an anonymous type:

var notifications = selectedNotificationsByUser
    .Select(n => new { n.NotificationGroupId, n.DeliveryTypeId })
    .Distinct()
    .ToList();

var result = _dbContext.NotificationGroupUserType
    .Include(m => m.NotificationGroup)
    .Include(m => m.DeliveryType)
    .Where(m => m.UserTypeId == (int)userType &&
        !notifications.Contains(new { m.NotificationGroupId, m.DeliveryTypeId }))
    .Select(m => new NotificationGroupUserType() {
        DeliveryType = m.DeliveryType,
        NotificationGroup = m.NotificationGroup
    })
    .ToList();

If LINQ-to-SQL cannot convert the expression to SQL, you can try this:

var result = _dbContext.NotificationGroupUserType
    .Include(m => m.NotificationGroup)
    .Include(m => m.DeliveryType)
    .Where(m => m.UserTypeId == (int)userType)
    .AsEnumerable() // Following part is LINQ-to-Objects
    .Where(m => !notifications.Contains(new { m.NotificationGroupId, m.DeliveryTypeId }))
    .Select(m => new NotificationGroupUserType() {
        DeliveryType = m.DeliveryType,
        NotificationGroup = m.NotificationGroup
    })
    .ToList();