5

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

  • AId (int - not null - identity - primary key)
  • AName (nvarchar(10) - not null)

B

  • BId (int - not null - identity - primary key)
  • SomeName (nvarchar(10) - not null)
  • AId (int - not null - foreign key connecting to AId in the table A)

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).

kmp
  • 10,535
  • 11
  • 75
  • 125

3 Answers3

9

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)));
leppie
  • 115,091
  • 17
  • 196
  • 297
  • Woah! So even though .All(b => names.Contains(b)) is functionally (I mean in terms of what it does, not how it does it) equivalent to .All(names.Contains) what you are saying is the underlying mechanism is different and I hit upon a bug in the EF when it is evaluating the expression tree? I just tried it and this "ctx.As.Where(a => a.Bs.Select(b => b.SomeName).All(b => names.Contains(b)))" does in fact execute without error! Thank you! – kmp Mar 01 '12 at 14:47
  • I hope you do not mind, I added a bit to your answer so that if anyone else comes across this they will see straight away the change you suggested. – kmp Mar 01 '12 at 14:51
  • @user1039947: No problem :) This is not an EF bug, but rather a missing feature with C#'s expression compiler. – leppie Mar 01 '12 at 16:43
  • I just put a bounty on a [similar question](http://stackoverflow.com/questions/11990158/internal-net-framework-data-provider-error-1025)... wanna grab it? – Shaul Behr Dec 13 '12 at 13:29
2

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!?

kmp
  • 10,535
  • 11
  • 75
  • 125
1

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 Bs' names with the fixed list gives all the Bs)

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 Bs' names is reduced to nothing by taking out the fixed list)

also untried.

AakashM
  • 62,551
  • 17
  • 151
  • 186