LINQ - Adding record where nested value contains value from another array

Question

I'm looking to add a notification record to my users with an SQL database using Entity Framework and LINQ. My users already have a nested array of tags that they have chosen. I would like to add a notification record to a user if their chosen tags contain any tag that is included in the array tags i pass into my method.

This is what my Tag array looks like:

[
  {
    "Id": 2,
    "Text": "Blue",
    "Value": "blue"
  },
  {
    "Id": 4,
    "Text": "Red",
    "Value": "red"
  },
  {
    "Id": 3,
    "Text": "White",
    "Value": "white"
  }
]

My Notification looks like this:

{
    "Image": "https://www.image.com",
    "Heading": "Example heading",
    "Content": "Example content"
}

My users have a nested array of tags associated with them that also looks like tags array above and have the same id's.

My method is currently like this:

AddUserNotification(Notification notification, IList<Tag> tags) 
{
    var usersToAddTheNotificationTo = DbContext.Users
        .Where(User.Tags.Contains(tags)).ToList();

    foreach(var user in usersToAddTheNotificationTo) 
    {
          user.Notifications.Add(notification);
    }
}

How can I achieve this is the most simple way?

Thanks Rufus, I cant get this to work. Where does the 'user' come from? — DBoi
– DBoi, Commented Jun 17, 2019 at 22:02
This should be better: .Where(user => user.Tags.Any(tag => tags.Contains(tag))) — littlerufe
– littlerufe, Commented Jun 17, 2019 at 22:04

Manuel Hess · Accepted Answer · 2019-06-17 22:14:00Z

1

what you want to do is building an intersection between the user's tags and the notification's tags and you want to add the notification if this new intersection quantity is not empty

var usersToAddTheNotificationTo = DbContext.Users
        .Where(User.Tags.Intersect(tags).Count() > 0).ToList();

answered Jun 17, 2019 at 22:14

Manuel Hess

13910 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

DBoi Over a year ago

Thanks. Will try this also. Can this be made async?

Manuel Hess Over a year ago

performing this operation async has no influence on the actual query. why would you want to perform it async?

DBoi Over a year ago

Ok very true. What are the advantages of using 'Intersect' instead of the 'Any' approach?

Manuel Hess Over a year ago

'Any' is a boolean function that tells you if a given quantity is not empty. You could also perform a 'Any()' instead of 'Count() > 0'

DBoi Over a year ago

Thanks for the help, will run this up and see if I can get it to work

Steve Py · Accepted Answer · 2019-06-17 23:52:41Z

You may run into issues associating Tags where these are not entities associated with the current context or query. Also, is the Notification a new entity, or does it represent a record that already exists in the database? Is the context expected to know about it?

When looking to associate based on criteria, I generally opt for working with IDs as this makes it simpler to query for matches. For example:

public void AddUserNotification(Notification notification, IList<int> tagIds) 
{
    var usersToAddTheNotificationTo = DbContext.Users
        .Where(x => x.Tags.Any(t => tagIds.Contains(t.TagId)).ToList();

    foreach(var user in usersToAddTheNotificationTo) 
    {
          user.Notifications.Add(notification);
    }
}

Without changing the signature:

public void AddUserNotification(Notification notification, IList<Tag> tags) 
{
    var tagIds = tags.Select(x => x.TagId).ToList();
    var usersToAddTheNotificationTo = DbContext.Users
        .Where(x => x.Tags.Any(t => tagIds.Contains(t.TagId)).ToList();

    foreach(var user in usersToAddTheNotificationTo) 
    {
          user.Notifications.Add(notification);
    }
}

One other caution: If Notification represents an existing record then you should also load it from the context based on the ID.

var existingNotification = DbContext.Notifications.Single(x => x.NotificationId == notification.NotificationId);

Entities passed in to things like Controller actions are deserialized, so as far as a DbContext is concerned they are no different to code like:

var notification = new Notification { NotificationId = 16, .... };

It doesn't matter that earlier the notification was loaded from a DbContext. It was a different DbContext instance, and the entity was serialized and deserialized. EF will treat it as a new record. If the PK is configured as an Identity, you will get a new record inserted with a new NotificationID. If the PK isn't configured, you'll get a key violation as EF attempts to insert a duplicate row. You can associate it to the DbContext using Attach() and set the State to "Modified" but this opens your system to a significant risk of tampering as clients could alter that entity in ways you don't expect, changing FKs or other values your UI doesn't permit and overwrite the data.

Collectives™ on Stack Overflow

LINQ - Adding record where nested value contains value from another array

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related