WP7 & Linq-To-SQL: SubmitChanges after query.toList() deletes items from db

Question

I would like to do the simplest thing - fetch data from db. When I make a query and later call SubmitChanges() on DataContext object, all the fetched items are deleted from DB. Here's the code:

SubmitChanges(); // nothing happened (it is OK)
List<Flashcard> list = (from f in FlashcardsTable
                        where f.Category.IsSelected
                              && !excluded.Contains(f.FlashcardId)
                        select f).ToList();
SubmitChanges(); // All items from list are deleting from DB!!

FlashcardsTable is a table in DB, excluded is just a list with integers.

My question is why data from DB is deleted when SubmitChanges() is called and how to fix it (I want to leave the data in the database).

---

I'm still searching answer for my questions... Here are more details from my code, maybe it helps?

I do 2 things in my app. First create 100 items and put them to DB:

var db = new DbContext();
var flashcard = new Flashcard();
// here sets some fields on flashcard, not important...
for (var i = 0; i < 100; i++)
{
    var cat = db.CategoriesTable.First(c => c.CategoryId == categoryId);
    cat.Count++;
    flashcard.Category = cat;
    flashcard.BasketNr = 1;
    flashcard.TimeToCheck = Utils.CurrentDate.AddHours(12);
    db.FlashcardsTable.InsertOnSubmit(new Flashcard(flashcard));
}
db.SubmitChanges();

Next I make a query on DB:

var db = new DbContext();
db.SubmitChanges(); // here is OK, no SQL was generated
var a = (from f in db.FlashcardsTable
         where f.Category.IsSelected
         select f).ToList();
db.SubmitChanges(); // here are generated 100 SQLs deleting my data

After call second db.SubmitChanges() are generated 100 SQLs like:

DELETE FROM [Flashcard] WHERE [FlashcardId] = @p0

-- @p0: Input Int32 (Size = 4; Prec = 0; Scale = 0) [1]

-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: System.Data.Linq, Version=7.0.0.0, Culture=neutral, PublicKeyToken=24EEC0D8C86CDA1E

Maybe definitions of Tables are wrong? Here it is:

[Table]
public class Category : INotifyPropertyChanged {

    public event PropertyChangedEventHandler PropertyChanged;

    private string name;
    [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
    public int CategoryId { get; set; }

    [Column(UpdateCheck = UpdateCheck.Never)]
    public string Name {
        get { return name; }
        set {
            if (name != value) {
                name = value;
                if (PropertyChanged != null) {
                    PropertyChanged(this, new PropertyChangedEventArgs("Name"));
                }
            }
        }
    }

    [Column(UpdateCheck = UpdateCheck.Never)]
    public int Count { get; set; }

    [Column(UpdateCheck = UpdateCheck.Never)]
    public bool IsSelected { get; set; }

}



[Table]
public class Flashcard {
    public const int TYPE_TEXT = 1;
    public const int TYPE_BITMAP = 2;

    [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
    public int FlashcardId { get; set; }

    [Column(UpdateCheck = UpdateCheck.Never)]
    public DateTime TimeToCheck { get; set; }

    [Column(CanBeNull = false, UpdateCheck = UpdateCheck.Never)]
    public int Type { get; set; }

    private byte[] firstPageBitmap;
    private byte[] secondPageBitmap;
    private string firstPageText;
    private string secondPageText;

    public Flashcard(Flashcard flashcard) {
        FirstPageBitmap = flashcard.FirstPageBitmap;
        SecondPageBitmap = flashcard.SecondPageBitmap;
        FirstPageType = flashcard.FirstPageType;
        SecondPageType = flashcard.SecondPageType;
        BasketNr = flashcard.BasketNr;
        Category = flashcard.Category;
        TimeToCheck = flashcard.TimeToCheck;
    }

    public Flashcard() {
    }

    [Column(DbType = "image", UpdateCheck = UpdateCheck.Never, CanBeNull = true)]
    public byte[] FirstPageBitmap {
        get { return firstPageBitmap; }
        set {
            FirstPageType = TYPE_BITMAP;
            firstPageBitmap = value;
        }
    }

    [Column(CanBeNull = true, UpdateCheck = UpdateCheck.Never)]
    public string FirstPageText {
        get { return firstPageText; }
        set {
            FirstPageType = TYPE_TEXT;
            firstPageText = value;
        }
    }

    [Column(UpdateCheck = UpdateCheck.Never)]
    public int FirstPageType { get; set; }

    [Column(UpdateCheck = UpdateCheck.Never)]
    public int SecondPageType { get; set; }

    [Column(DbType = "image", UpdateCheck = UpdateCheck.Never, CanBeNull = true)]
    public byte[] SecondPageBitmap {
        get { return secondPageBitmap; }
        set {
            SecondPageType = TYPE_BITMAP;
            secondPageBitmap = value;
        }
    }

    [Column(CanBeNull = true, UpdateCheck = UpdateCheck.Never)]
    public string SecondPageText {
        get { return secondPageText; }
        set {
            SecondPageType = TYPE_TEXT;
            secondPageText = value;
        }
    }

    [Column(UpdateCheck = UpdateCheck.Never)]
    public int BasketNr { get; set; }

    [Column(UpdateCheck = UpdateCheck.Never)]
    public int CatId { get; set; }

    [Association(ThisKey = "CatId", OtherKey = "CategoryId", IsForeignKey = true, DeleteOnNull = true)]
    public Category Category { get; set; }
}

Any suggestions?

Answer 1

Make sure you tell Linq what you want to do to the tables individually

FlashcardsTable.DeleteAllOnSubmit(list);
    SubmitChanges();

This is a simple way to delete all of them

Answer 2

I am 99.999999% sure this is a case of an inproper diagnoses.

Perhaps your data is realy deleted from the database but I am confident it is not because of this piece of code. Show us the real code (in this example you do not have a datacontext so it is some kind of simplification..)

You can profile the sql generated to check if there is a delete in there. If so, I will be more than glad to add a bounty to your question.

Answer 3

I figured it out. After change declaration of association Flashcard->Category everything works fine. Right code is here:

private EntityRef<Category> _categoryRef = new EntityRef<Category>();
[Association(ThisKey = "CatId", OtherKey = "CategoryId", IsForeignKey = true, DeleteOnNull = true, Storage = "_categoryRef")]
public Category Category
{
    get { return _categoryRef.Entity; }
    set
    {
        if (_categoryRef.Entity != value || !_categoryRef.HasLoadedOrAssignedValue)
        {
            _categoryRef.Entity = value;
        }
    }
}