How can I get multiple objects using a combo key?
I am using Entity Framework (4.2 if it matters) and I have a model that has a composite primary key (two int columns). I have a list of int pairs that represent a list of objects that I would like to pull from the database. Is there an easy way to pull all of these objects with a single query?
I tried to join a request with my list like this:
List<Tuple<int, int>> ids = GetIds();
var data =
from e in ctx.CompositeEntities
join i in ids on new {e.KeyA, e.KeyB} equals new {KeyA = i.Item1, KeyB = i.Item2}
select e;
However, this always throws the exception "Unable to create constant value of type 'System.Tuple`2'. Only primitive types (such as Int32, String, and Guid) are supported in this context."
source to share
I would like to see a cleaner way to do this, but ... at least it should work.
Solution1
What you need is to concatenate the list of tuples, then concatenate the "int keys" with your entity using SqlFunctions.StringConvert . Since only double or decimal is required as parameters, you need to cast your int to double first.
List<Tuple<int, int>> ids = GetIds();
var concatenatedIds = ids.Select(m => m.Item1 + "," + m.Item2).ToList();
var result = ctx.CompositeEntities
.Where(conatenatedIds.Contains(
SqlFunctions.StringConvert((double)m.KeyA) +
"," +
SqlFunctions.StringConvert((double)m.KeyB)));
Solution2
Use PredicateBuilder
List<Tuple<int, int>> ids = GetIds();
var predicate = Predicate.False<CompositeEntity>();
foreach (var tuple in ids)
predicate = predicate.Or(m => m.KeyA == tuple.Item1 && m.KeyB == tuple.Item2);
var result = ctx.CompositeEntities.Where(predicate);
Solution3
List first, then compare. Of course, this will result in all CompositeEntities being retrieved from the database.
source to share
I can't try this, but try using something like this where clause.
from e in ctx.CompositeEntities
where ids.Where(i => e.KeyA == i.Item1 && e.KeyB == i.Item2).Count() > 0
select e;
===== EDIT =====
Good thing it fails with the same exception, thanks for the support.
How about the following, assuming the key is an int and not a bigint
public class e1 {
public int KeyA { get; set; }
public int KeyB { get; set; }
}
public class e1Configuration : EntityTypeConfiguration<e1> {
public e1Configuration()
: base() {
HasKey(x => new { x.KeyA, x.KeyB });
}
}
public class TestEFContext : DbContext {
public IDbSet<e1> es { 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 e1Configuration());
}
}
class Program {
static void Main(String[] args) {
using (TestEFContext c = new TestEFContext(@"Data Source=ALIASTVALK;Initial Catalog=TestEF;Integrated Security=True; MultipleActiveResultSets=True")) {
c.es.Add(new e1 { KeyA = 1, KeyB = 1 });
c.es.Add(new e1 { KeyA = 1, KeyB = 2 });
c.es.Add(new e1 { KeyA = Int32.MaxValue, KeyB = 2 });
c.SaveChanges();
}
List<Tuple<int, int>> ids = new List<Tuple<int, int>> {
new Tuple<int, int>(1, 1),
new Tuple<int, int>(Int32.MaxValue, 2),};
List<Int64> lis = (from t in ids select (Int64)t.Item1 * 2^ 32 + t.Item2).ToList();
using (TestEFContext c0 = new TestEFContext(@"Data Source=ALIASTVALK;Initial Catalog=TestEF;Integrated Security=True; MultipleActiveResultSets=True")) {
var v = from e in c0.es
where lis.Contains((Int64)e.KeyA * 2 ^ 32 + e.KeyB)
select e;
Console.WriteLine(v.Count());
}
}
}
source to share
I am working with a nearly identical scenario, but I will try to match it to the one above, so let me know if it doesn't compile.
This is the same basic idea of Raphaël 2, but I couldn't get it. I am creating Predicate as a pooling of queries for individual objects, one for each tuple.
List<Tuple<int, int>> ids = GetIds();
ids.Aggregate<Tuple<int,int>,IQueryable<CompositeEntity>>(
null,
(current, id) =>
current == null ? SingleCompositeEntity(ctx.CompositeEntities, id)
: current.Union(SingleCompositeEntity(ctx.CompositeEntities, id)));
}
private static IQueryable<CompositeEntity>
SingleCompositeEntity(IQueryable source, Tuple<int,int> tuple)
{
return source.Where(m => m.KeyA == tuple.Item1 && m.KeyB == tuple.Item2)
}
source to share
Unconfirmed, but I think it will work.
Add a property to the Entity to expose the keys as a tuple.
public Tuple<int, int> TupleKey
{
get { return new Tuple<int, int>(_keyA, _keyB);}
}
Then modify your query to use this new property.
List<Tuple<int, int>> ids = GetIds();
var data = from e in ctx.CompositeEntities
where ids.Contains(e.TupleKey)
select e;
source to share