My program is more complex, but basically the problem is like this -- there is table Customer and table City. In Customor I have field cityId and foreign key for it.

Now I can write such code

customer.cityIdCity = myCity;

or

customer.cityId = 7;

The issue is I cannot use the first form, because cities are cached, and in such case the data would be "hijacked" by current data context (and exception would occur), so I prefer using the second form. However when I submit changes for customer I get exception about inconsistency within data -- which is honestly speaking true -- cityId is 7, but cityIdCity is null.

The question is -- for the second form, how to force LINQ to focus on id (and execute simple insert with id used), and accept missing object.

Edits

1

The field cityId is just an int (as in table), but sqlmetal also created another property for mapping class cityIdCi开发者_StackOverflow中文版ty which is reference to City class (from table City).

Internally it looks like this:

    private EntityRef<City> _cityIdCity;

There is also a long property cityIdCity which sets the above field and handles event triggering for changing and changed state of the field.

And regular field (int) is defined like this:

    private System.Nullable<int> _cityId;

plus analogous property.

2

I found out it is due to fetching data. Once data are loaded (city object for customer in this case), Linq To SQL assumes it is fixed, i.e. id and city reference have to be in sync. If you don't read them before changing them it is ok, because L2S sets new values but also fetches new data from DB on fly.

And I have to read city of customer before I change it.

3

The steps are as follows:

1. find in DB a customer
2. it does not exist? --> create record
3. is the city of customer the same as the incoming data (city)? if yes, go to step (8)
4. check in cache if we have appropriate city, if yes go to (7)
5. create city record
6. save city
7. bind city and customer
8. save customer

So in (3) there is read of city field of customer, and in (7) there is either complain about using data from another context or fields out of sync (case when data are changed).

For comparison I tested several cases using Entity Framework and it behaves more straightforward, i.e. there are no problems in both approaches (when changing referenced field with whole object or just id).

4

This is simplified code, but shows the problem.

using (var Db = new L2S.DataClasses1DataContext())
{
    var customer = Db.Customers.Single(it => it.cust_Id==2);
    customer.Name = "New name";
    Console.WriteLine(customer.City.city_Name); // loading city from DB
    // cannot change, because we would be out of sync with referenced object
    customer.city_Id = 57834; 
                            
    Db.SubmitChanges();
}

5 -- thin vs. fat data context

Using the same example as Pleun. This is fat DC:

using (var Db = new L2S.DataClasses1DataContext())
{    
   ...
   customer.City = Db.Cities.Single (i=> i.id =   57834 ); 
   Db.SubmitChanges();
 }

because as you can see customer uses the same DC as the cities (cities are cached!). This is wrong, because every malformed data from customer will creep to DC (cache). So an error from an hour ago, will also be present now.

This is thin DC:

var city = CacheDb.Cities.Single (i=> i.id =   57834 ); 

...

using (var Db = new L2S.DataClasses1DataContext())
{    
   ...
   customer.City = city; 
   Db.SubmitChanges();
 }

However this won't work because L2S does not allow sharing entities between DC (in typical case between cache and working -- updating -- DC).

---

Have you tried

using (var Db = new L2S.DataClasses1DataContext())
{    
 ...
 // leave out customer.city_Id = 57834;  but replace with
 customer.City = Db.Cities.Single (i=> i.id =   57834 ); 
 Db.SubmitChanges();
 }