CODEWITHSUNDEEP
c#linqentity-frameworkcontainsanonymous-types
p.s.w.g
p.s.w.g

Reputation: 149050

Effective linq contains with anonymous type

I have a collection of anonymous objects created like this:

var srcCategories = srcSet.Categories.Select(c => new
{
     ApplicationId = c.IsGLobal ? (long?)null : c.App.Id,
     c.Name
});

Note that this collection does not come from my data context; it's generated from input from an external system. I need to map both ApplicationId and Name with entities in my database. So far this is the only way I've been able to successfully make it work:

var trgCategoryIds =
    (from c in core.Domain.Categories.AsEnumerable()
     let ci = new { c.ApplicationId, c.Name }
     where srcCategories.Contains(ci)
     select c.Id)
    .ToArray();

But this requires me to pull the entire Categories table into memory first. I'm looking for a more efficient way to do this, preferably in a single query. I've tried all of the following options, none of them can be converted to sql:

// Removed .AsEnumerable()
var trgCategoryIds =
    (from c in core.Domain.Categories 
     let ci = new { c.ApplicationId, c.Name }
     where srcCategories.Contains(ci)
     select c.Id)
     .ToArray();

// Use .Any() instead of .Contains()
var trgCategoryIds =
    (from c in core.Domain.Categories
     where srcCategories.Any(s => s.ApplicationId == c.ApplicationId && s.Name == s.Name)
     select c.Id)
    .ToArray();

// Use Tuples instead of anon types
var srcCategories = srcSet.Categories.Select(c => Tuple.Create(...));
var trgCategoryIds =
    (from c in core.Domain.Categories
     let ci = Tuple.Create(c.ApplicationId, c.Name)
     where srcCategories.Contains(ci)
     select c.Id)
    .ToArray();

Upvotes: 1

Views: 2694

Answers (4)

Bobson
Bobson

Reputation: 13706

What you want to do isn't really possible, because there's no easy SQL for it in the first place. Effectively, you want:

select * from Catagories where (ApplicationID = 1 and Name = "Foo") 
                            or (ApplicationID = 2 and Name = "Bar") 
                            or (ApplicationID = 2345 and Name = "Fizbuzz")
                            or ...

Entity Framework, to the best of my knowledge, can't create that type of query automatically. It can handle a single test by converting Contains() to IN (...), but there's no simple SQL for an anded IN when you can't join. However, you can use the Predicate Builder library to construct this type of OR query. The second example on the page should be just what you need.

Adapted for your usage:

var predicate = PredicateBuilder.False<Category>();

  foreach (var cat in srcCategories)
  {
    var temp = cat;
    predicate = predicate.Or (p => p.ApplicationId == temp.ApplicationId && p.Name == temp.Name);
  }
  return core.Domain.Categories.AsExpandable().Where (predicate);
}

Upvotes: 3

Servy
Servy

Reputation: 203843

If the two Categories collections come from different database contexts then there is no way to get around pulling one of the two into memory.

If they share a database context then what you are trying to do is to simply join the two tables:

var query =
    from domainCat in srcCategories
    join sourceCat in srcSet.Categories
    on new { domainCat.ApplicationId, domainCat.Name } equals
        new { sourceCat.ApplicationId, sourceCat.Name }
    select sourceCat.Id;

Upvotes: 1

tschmit007
tschmit007

Reputation: 7800

what about adaptating the following code, with ks as the formatted categories (Id + "-" + Name).

using System;
using System.Linq;
using System.Data.Entity;
using System.Collections.Generic;
using System.Data.Entity.ModelConfiguration;
using System.Data.Objects.SqlClient;

namespace testef {
    //Model
    public class CObj {
        public CObj() {
        }
        public Int32 Id { get; set; }
        public String SomeCol { get; set; }
    }

    //Configuration for CObj
    public class CObjConfiguration : EntityTypeConfiguration<CObj> {
        public CObjConfiguration() {
            HasKey(x => x.Id);
        }
    }

    public class TestEFContext : DbContext {
        public IDbSet<CObj> objects { get; set; }

        public TestEFContext(String cs)
            : base(cs) {
            Database.SetInitializer<TestEFContext>(new DropCreateDatabaseAlways<TestEFContext>());
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder) {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Configurations.Add(new CObjConfiguration());
        }
    }

    class Program {
        static void Main(String[] args) {
            String cs = @"Data Source=ALIASTVALK;Initial Catalog=TestEF;Integrated Security=True; MultipleActiveResultSets=True";                

            using (TestEFContext c = new TestEFContext(cs)) {
                c.objects.Add(new CObj { Id = 1, SomeCol = "c"});
                c.SaveChanges();
            }

            IEnumerable<String> ks = new List<String> { String.Format("{0,10}-c", 1) };
            foreach (var k in ks) {
                Console.WriteLine(k);
            }

            using (TestEFContext c = new TestEFContext(cs)) {
                var vs = from o in c.objects
                         where ks.Contains(SqlFunctions.StringConvert((Decimal?)o.Id, 10) + "-" + o.SomeCol)
                         select o;

                foreach (var v in vs) {
                    Console.WriteLine(v.Id);
                }
            }

        }
    }
}

Upvotes: 0

AD.Net
AD.Net

Reputation: 13399

var trgCategoryIds =
    (from c in core.Domain.Categories.AsEnumerable()
     where sourceCategories.Any(sc=> sc.ApplicationId == c.ApplicationId 
                     && sc.Name == c.Name)
     select c.Id)
    .ToArray();

Upvotes: -1

Related Questions