Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get around "Internal .NET Framework Data Provider error 1025."?

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

like image 468
kmp Avatar asked Mar 01 '12 09:03

kmp


3 Answers

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)));
like image 81
leppie Avatar answered Sep 19 '22 13:09

leppie


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

like image 29
kmp Avatar answered Sep 20 '22 13:09

kmp


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.

like image 38
AakashM Avatar answered Sep 20 '22 13:09

AakashM