Returning a collection of objects where an objects property matches any property from another collection of objects using LINQ-to-Entities

Question

I've been searching all day and can't find a solution to this...

I have an EntityCollection of Communication objects which each have an instance of an Intention object(one-to-one).

I also have a User object which has many instances of UserLocation EntityObjects(one-to-many)

• Intention objects have a property UID.

• UserLocation objects have a property LID.

• I want to write a LINQ expression which returns all Communication objects where the UID property of the Intention instance associated to a Communication object equals ANY LID property of ANY instance of a UserLocation instance for a User object.

I've tried this

return _context.Communications.Where
(u => u.Intention.UID.Equals
(user.UserLocations.Select
(p => p.LID)));

and this

return _context.Communications.Where
(u => user.UserLocations.Any
(x => x.LID.Equals
(u.Intention.UID)));

and this

var thislist = from Intentions in _context.Intentions
                           join UserLocations in user.UserLocations
                           on Intentions.UID equals UserLocations.LID
                           select Intentions.UID;
            return _context.Communications.Where(u => u.Intention.Equals(thislist.Any()));

and this

var lidlist = user.UserLocations.Select(x => x.LID);
return _context.Communications.Where(x=> lidlist.Contains(x.Intention.UID)).ToList();

(this gives me an error on the Contains statement saying "Delegate System.Func<Communication,int,bool> does not take 1 argument", don't know how to fix)

Along with all these variations I have also:

• modified my method to return IQueryable<Communication> and have also tried List<Communication> while appending ToList() to my queries.

Nothing works. Regardless of what I try I always end up with this exception

NotSupportedException was unhandled by user code

Unable to create a constant value of type 'PreparisCore.BusinessEntities.UserLocation'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

What am i doing wrong??

Lawry

what are the types of UID and LID ? – Elsie

Hmm, than second form should work. I'd use = for clarity. I will try to write a test case to confirm. – Elsie

Which delegate does not take 1 argument? Normally the exception specifies the delegate type exactly. What type does your method return? The last code should work in my opinion. The other three indeed cannot work. – Studio

I think I found an issue with second form ... BRB – Elsie

@Studio System.Func<BusinesEntities.Communicaton,int,bool> – Lawry

Is it a compile time or a runtime error/exception? – Studio

It's a runtime exception, everything compiles fine – Lawry

I meant the "Delegate xxx does not take 1 argument" error (sorry, wasn't clear about that). Is this a runtime exception as well, or compiler error? (You are saying "error on the Contains statement" which sounds more like a compiler message...) And BTW: Which EF version are you using? – Studio

Ah, that is a compiler error. The main exception(in my question) is a runtime error. Running .net 4 – Lawry

I can reproduce the compiler error when in Where(x=> lidlist.Contains(x.Intention.UID)) anything is spelled wrong, for example: lidxyzlist or Contain or Intenton or AID or... The message is very strange because it doesn't say "this or that variable/property is undeclared which I had expected. But if I fix all spelling errors it compiles (and I believe it will run correctly without exception then). Can you double check if everything is written correctly? – Studio

I double checked, everything is correct. However @Elsie discovered my problem in the answer below - I needed .ToList() appended to my Communication and Intention objects. Doh! – Lawry

Can you check the compiler error in the compiler output window if there are more than only one error? I get your error message in the tooltip window when I hover over the error squiggle line, but in the output window is actually more than one error that might give better indications what is wrong. (I've asked here about the problem: #11738917, see especially the Edit in the question.) – Studio

Hi, I tried compiling this morning and get these errors: i.imgur.com/jaONi.png Reading through your question, I'll also add that I get squigglies under x => lidlist.Contains(x.Intention.UID)) – Lawry

Answer 1

Given this code:

namespace CollectionsWithIntentions
{
    using System.Collections.Generic;
    using System.Diagnostics;
    using System.Linq;

    internal class Program
    {
        #region Methods

        private static void Main(string[] args)
        {
            var communications = new[]
                {
                    new Communication { Intention = new Intention { UID = 1 } },
                    new Communication { Intention = new Intention { UID = 2 } },
                    new Communication { Intention = new Intention { UID = 3 } },
                    new Communication { Intention = new Intention { UID = 4 } },
                };
            var users = new[]
                {
                    new User { UserLocations = new List<UserLocation>(new[] { new UserLocation { LID = 2 },new UserLocation{LID=5}  }) },
                    new User { UserLocations = new List<UserLocation>(new[] { new UserLocation { LID = 3 } }) }
                };

            IEnumerable<Communication> res =
                communications.Where(w => users.Any(a => a.UserLocations.Any(b=>b.LID == w.Intention.UID)));
            foreach (Communication communication in res)
            {
                Trace.WriteLine(communication);
            }
        }

        #endregion
    }

    internal class Communication
    {
        #region Public Properties

        public Intention Intention { get; set; }

        #endregion

        #region Public Methods and Operators

        public override string ToString()
        {
            return string.Concat("Communication-> Intention:", this.Intention.UID);
        }

        #endregion
    }

    internal class Intention
    {
        #region Public Properties

        public int UID { get; set; }

        #endregion
    }

    internal class User
    {
        #region Public Properties

        public List<UserLocation> UserLocations { get; set; }

        #endregion
    }

    internal class UserLocation
    {
        #region Public Properties

        public int LID { get; set; }

        #endregion
    }
}

I get this result:

Communication-> Intention:2
Communication-> Intention:3

Am I missing anything?

Elsie

One correction, I'm searching through just one user, but your code looks almost exactly the same as my second example. another difference to note is that all of these objects are either EntityObjects(UserLocation, User, Communication, Intention) or EntityCollections(Userlocations). UserLocations is a "bridging" object between two entites "User" and "Location". – Lawry

I can confirm that going through EF causes the same error to be encountered ... Investigating – Elsie

The answer is available in this post. So in your case you will have to cast Communications and UserLocations to a list, first. Than it works: ctx.Communications.ToList().Where(w1 => user.Locations.ToList().Any(a1 => a1.LID == w1.Intentions.UID)) .ToList(); – Elsie

You are fantastic :) This was it! I feel like apply hand to forehand is in order here. Thank you for the help!! – Lawry

@MattFoxxDuncan and Darek: The solution in the comment is pretty evil because it will load the whole Communications table first into memory before the filter is applied at all. Might not be a problem for 100 records in that table, but for 100000 or a million? – Studio

@Studio yes, indeed. So you better filter it before hand. Alternatively you could pursue this path, but it is not perfect either. – Elsie

@MattFoxxDuncan and Darek: Actually the last query in the question should work. I opened a question because I don't understand exactly the compiler error this query is throwing: #11738917 – Studio

@Studio thanks for the heads up on performance problems later down the line. I will be adding some filtering functionality. – Lawry

Answer 2

From the last two compiler errors you have linked in one of your comments...

...I would conclude that Intention.UID is a nullable type int? and not a not-nullable int as you said in the comments. This indeed doesn't compile. Try to change your last query to:

var lidlist = user.UserLocations.Select(x => x.LID);
return _context.Communications
    .Where(x => x.Intention.UID.HasValue
             && lidlist.Contains(x.Intention.UID.Value))
    .ToList();

The other three queries do not work because user.UserLocations is a collection of a non-primitive custom type in memory (for the SQL query to be generated it is a "constant" value) and EF doesn't support to build a SQL query with such a constant custom type.

Studio