Select via Linq and EF with empty list for linked tables

Question

I want to select a list via Linq and EF.

The problem is that if I use lazy load and then try to clear a property that links to another table (foo.prop.clear()) it takes very long time (goes back to the DB).

If I use .include(x => x.props) it returns all the rows from the linked table, which also takes a lot of time.

I don't want to use the .Select() option since the linked table can change.

Is there a way to get the foo.prop already empty, or clear the list faster?

Thanks in advance.

Call directly to sql level. http://www.entityframeworktutorial.net/entityframework4.3/raw-sql-query-in-entity-framework.aspx — ZorgoZ, Aug 19 '18 at 20:22
What? Issuing a delete statemant to remove the rows you would clear is never lazy load. — ZorgoZ, Aug 19 '18 at 20:44
I don't want to delete. just to clear the foo.props list and replace with my own data. the clear takes to long. — Tal, Aug 19 '18 at 20:51
If you want to break the reference, than you actually do clear a table field. This can also be done quickly with an update statement. EF might not figure out that many updates can be simplified to just one - you can. "Replacing" is an other stuff as that is insert, which can take much longer. But is you break a reference that is mandatory, you need to delete the referencing entity, or you will get exception. — ZorgoZ, Aug 19 '18 at 21:26
can you show your code? I am confused about what your trying to achieve? — JanR, Aug 20 '18 at 06:52
Let's say I have a table of questions that linked to a table of answers. I want to get all the questions, but each question has a list of answer that I need to clear. So I tried: var answers = db.answer.where(a => a.id > 100).ToList(); answers.questions.clear(); the clear() takes to long. — Tal, Aug 20 '18 at 08:59

score 0 · Answer 1 · answered Aug 20 '18 at 12:04

Although you forgot to specify, it seems to me that every Foo has zero or more Props, and every Prop belongs to exactly one Foo: a standard one-to-many relation. It could be a many-to-many relation, the answer will be similar.

Apparently you want to remove all Props from one or more Foos. Entity framework is nt designed to fetch and change data in one database access. You'll have to fetch the objects you want to change, change the values of one or more properties of the fetched objects and then call SaveChanges().

If you want to fetch-change-save in one function, you'll have to write a stored procedure.

The Entity Framework Method

If you have followed the entity framework code first conventions you'll have something like:

class Foo
{
    public int Id {get; set;}
    ...

    // every Foo has zero or more Props (one-to-many)
    public virtual ICollection<Prop> Props {get; set;}
}

class Prop
{
    public int Id {get; set;}
    ...
    // every Prop belongs to exactly one Foo using foreign key:
    public int FooId {get; set;}
    public virtual Foo Foo {get; set;}
}

One would expect that once you'd have fetched a Foo it would be enough to call foo.Props.Clear():

var fetchedFoos = dbContext.Foos.Where(foo => ...);
foreach (var fetchedFoo in fetchedFoos)
{
    fetchedFoo.Props.Clear();
}

Alas, as is stated in this Stackoverflow article, ICollection.Clear() does not work in entity framework (at least not in the older versions. I haven't checked it lately).

If you want to get rid of all Props of a given Foo, you'll have to delete them by Prop.FooId

Create the query that will fetch all Foos of which you want to clear the Props collection. Do not execute the query yet:

var fooIds = myDbContext.Foos
    .Where(foo => ...)
    // I don't need the complete `Foo`, I only need the `Id`
    .Select(foo => foo.Id);

I want to remove all Props that have a FooId that is somewhere in fooIds:

var propsToDelete = dbContext.Props
    .Where(prop => fooIds.Contains(prop.FooId));

Query still not executed!

Now delete the props:

dbContext.Props.RemoveRange(propsToDelete);
dbContext.SaveChanges();

If desired, you can concatenate the LINQ into one big statement. As no queries are executed until the RemoveRange / SaveChanges I doubt whether this would improve efficiency. It surely will deteriorate readability, and thus testability / maintainability

Thank you for your answer. But I don't want to save any changes. just clear the props list in the code side and add new item. I'm using DB first. — Tal, Aug 20 '18 at 12:38

Select via Linq and EF with empty list for linked tables

1 Answers1

The Entity Framework Method