I am using the Entity Framework 4.3, POCO, database first and I am getting the following error:
Internal .NET Framework Data Provider error 1025.
QUESTION: I think that my query expresses my intent but I seem to be hitting this error, so I am wondering if anyone knows how I could structure my query differently to get around this error?
Here is the scenario...
I have a SQL server 2008 database that has 2 tables - A and B:
A
B
I then define the context like so:
public class DatabaseContext : DbContext
{
public DatabaseContext(string name)
: base(name)
{
Configuration.AutoDetectChangesEnabled = false;
As = Set<A>();
Bs = Set<B>();
}
public DbSet<A> As { get; private set; }
public DbSet<B> Bs { get; private set; }
}
And the entity classes like so:
public class A
{
public int AId { get; set; }
public string AName { get; set; }
public virtual ICollection<B> Bs { get; private set; }
public void AddB(B b)
{
if (b == null)
{
throw new ArgumentNullException("b");
}
if (Bs == null)
{
Bs = new List<B>();
}
if (!Bs.Contains(b))
{
Bs.Add(b);
}
b.A = this;
}
}
public class B
{
public int BId { get; set; }
public A A { get; set; }
public string SomeName { get; set; }
}
Now for the query...
What I want is all of the As where every "B SomeName" is in the list of names supplied so I do this:
var names = new[] {"Name1", "Name2"};
var ctx = new DatabaseContext("EFPlayingEntities");
var res = ctx.As.Where(a => a.Bs.Select(b => b.SomeName).All(names.Contains));
// Here I evaluate the query and I get:
// Internal .NET Framework Data Provider error 1025.
Console.WriteLine(res.Count());
To be clear about what I mean, if the table data looks like this:
AId,AName
1,A1
2,A2
3,A3
4,A4
BId,SomeName,AId
1,Name1,1
2,Name2,1
3,Name1,2
4,Name1,3
5,Name3,3
6,Name1,4
7,Name2,4
I would expect to get back A1, A2 and A4 (so that count call above would return 3).
The reason why this happens is subtle.
Queryable.All
need to be called with an Expression
. Passing in just the method 'reference' creates a delegate, and subsequently, Enumerable.All
becomes the candidate instead of the intended Queryable.All
.
This is why your solution you posted as an answer works correctly.
EDIT
so if you write the statement as this, it will work without exception:
var res = ctx.As.Where(
a => a.Bs.Select(b => b.SomeName).All(b => names.Contains(b)));
I have worked out a solution to this, in case anyone is interested. Doing the following is equivalent and does not result in the exception in the question:
var res = ctx
.Bs
.GroupBy(b => b.A)
.Where(g => g.All(b => names.Contains(b.SomeName)))
.Select(g => g.Key);
I do not know if this is the best way though!?
The semantics of your query look good to me; clearly, getting an internal provider error is not the intended behaviour! I would have expected some more explicit mesage about EF not being able to translate your query into a store operation, if that's in fact what the problem is.
Another way to do what you want would be:
var names = new[] {"Name1", "Name2"};
var nameCount = names.Length;
var ctx = new DatabaseContext("EFPlayingEntities");
var result = ctx.As
.Where(a => a.Bs
.Select(b => b.SomeName)
.Intersect(names)
.Count() == a.Bs.Count());
(get every A
such that intersecting its B
s' names with the fixed list gives all the B
s)
although I haven't tried this to see if EF can translate this successfully.
Another way:
var names = new[] {"Name1", "Name2"};
var ctx = new DatabaseContext("EFPlayingEntities");
var result = ctx.As
.Where(a => !a.Bs.Select(b => b.SomeName).Except(names).Any());
(get every A
such that the list of its B
s' names is reduced to nothing by taking out the fixed list)
also untried.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With