Operation Intersect with linq

Question

Sorry for strange title of the question, but I don't know how to formulate it more short. If you know how to formulate it better, I will be glad if you edit my question. So, I have the following table:

I'm tolking about CustomerId and EventType fields. The rest is not important. I think you understand that this table is something like log by customers events. Some customer make event - I have event in the table. Simple.

I need to choice all customers events where each customer had event with type registration and type deposit. In other words, customer had registration before? The same customer had deposit? If yes and yes - I need to select all events of this customer.

How I can do that with the help of LINQ?

So I can write SQL like

select *
From "CustomerEvents"
where "CustomerId" in (
    select distinct "CustomerId"
    from "CustomerEvents"
    where "EventType" = 'deposit'

    intersect

    select distinct "CustomerId"
    from "CustomerEvents"
    where "EventType" = 'registration'
)

It works, but how to write it on LINQ?

And second question. SQL above works, but not it is not universal. What if tomorrow I will need to show events of customers who have registration, deposit and - new one event - visit? I have to write new one query. Like:

select *
From "CustomerEvents"
where "CustomerId" in (
    select "CustomerId"
    from "CustomerEvents"
    where "EventType" = 'deposit'

    intersect

    select distinct "CustomerId"
    from "CustomerEvents"
    where "EventType" = 'registration'

     intersect

    select distinct "CustomerId"
    from "CustomerEvents"
    where "EventType" = 'visit'
)

Uncomfortable :( As source data, I have List with event types. Is there some way to make it dynamically? I mean, I have new one event in the list - I have new one intersect.

P.S I use Postgres and .NET Core 3.1

Update

I pine here a scheme

Answer 1

I haven't tested to see if this will translate to SQL correctly, but if we assume ctx.CustomerEvents is DbSet<CustomerEvent> you could try this:

var targetCustomerIds = ctx
  .CustomerEvents
  .GroupBy(event => event.CustomerId)
  .Where(grouped => 
    grouped.Any(event => event.EventType == "deposit")
    && grouped.Any(event => event.EventType == "registration"))
  .Select(x => x.Key)
  .ToList();

and then select all events for these customers:

var events = ctx.CustomerEvents.Where(event => targetCustomerIds.Contains(event.CustomerId));

To get targetCustomerIds dynamically with a variable number of event types, you could try this:

// for example
var requiredEventTypes = new [] { "deposit", "registration" };

// First group by customer ID
var groupedByCustomerId = ctx
  .CustomerEvents
  .GroupBy(event => event.CustomerId);

// Then filter out any grouping which doesn't satisfy your condition
var filtered = GetFilteredGroups(groupedByCustomerId, requiredEventTypes);

// Then select the target customer IDs
var targetCustomerIds = filtered.Select(x => x.Key).ToList();

// Finally, select your target events
var events = ctx.CustomerEvents.Where(event => 
  targetCustomerIds.Contains(event.CustomerId));

You can define the GetFilteredGroups method like this:

private static IQueryable<IGrouping<int, CustomerEvent>> GetFilteredGroups(
    IQueryable<IGrouping<int, CustomerEvent>> grouping,
    IEnumerable<string> requiredEventTypes)
{
    var result = grouping.Where(x => true);
    foreach (var eventType in requiredEventTypes)
    {
        result = result.Where(x => x.Any(event => event.EventType == eventType));
    }

    return result;
}

Alternatively, instead of selecting the target customer IDs, you can try to directly select your target events from the filtered groupings:

// ...
// Filter out any grouping which doesn't satisfy your condition
var filtered = GetFilteredGroups(groupedByCustomerId, requiredEventTypes);

// Select your events here
var results = filtered.SelectMany(x => x).Distinct().ToList();

Regarding the inability to translate the query to SQL Depending on your database size and particularly on the size of CustomerEvents table, this solution may or may not be ideal, but what you could do is load the optimized collection to memory and perform the grouping there:

// for example
var requiredEventTypes = new [] { "deposit", "registration" };

// First group by customer ID, but load into memory
var groupedByCustomerId = ctx
  .CustomerEvents
  .Where(event => requiredEventTypes.Contains(event.EventType))
  .Select(event => new CustomerEventViewModel 
    { 
      Id = event.Id, 
      CustomerId = event.CustomerId, 
      EventType = event.EventType 
    })
  .GroupBy(event => event.CustomerId)
  .AsEnumerable();

// Then filter out any grouping which doesn't satisfy your condition
var filtered = GetFilteredGroups(groupedByCustomerId, requiredEventTypes);

// Then select the target customer IDs
var targetCustomerIds = filtered.Select(x => x.Key).ToList();

// Finally, select your target events
var events = ctx.CustomerEvents.Where(event => 
  targetCustomerIds.Contains(event.CustomerId));

You will need to create a type called CustomerEventViewModel like this (so you don't have to load the entire CustomerEvent entity instances to memory):

public class CustomerEventViewModel
{
  public int Id { get; set; }
  public int CustomerId { get; set; }
  public string EventType { get; set; }
}

And change the GetFilteredGroups like this:

private static IEnumerable<IGrouping<int, CustomerEvent>> GetFilteredGroups(
    IEnumerable<IGrouping<int, CustomerEvent>> grouping,
    IEnumerable<string> requiredEventTypes)
{
    var result = grouping.Where(x => true);
    foreach (var eventType in requiredEventTypes)
    {
        result = result.Where(x => x.Any(event => event.EventType == eventType));
    }

    return result;
}

It should now work fine.

Answer 2

Thank you for @Dejan Janjušević. He is excpirienced developer. But it seems EF can't translate him solution to SQL (or just my hands grow from wrong place). I publish here my solution for this situation. It's simple stuped. So. I have in the table EventType. It is string. And I have from the client the following filter request:

List<string> eventType

Just list with event types. So, in the action I have the following code of the filter:

if (eventType.Any())
{
    List<int> ids = new List<int>();

    foreach (var e in eventType)
    {
        var customerIdsList =
            _context.customerEvents.Where(x => x.EventType == e).Select(x => x.CustomerId.Value).Distinct().ToList();

        if (!ids.Any())
        {
            ids = customerIdsList;
        }
        else
        {
            ids = ids.Intersect(customerIdsList).ToList();
        }
    }

    customerEvents = customerEvents.Where(x => ids.Contains(x.CustomerId.Value));
}

Not very fast, but works.