One to one relation anomaly

Question

I am a bit confused by the behaviour of LINQ To SQL (just for clarity I do not mean EntiyFramework or LINQ To Entity but old LINQ To SQL).

I have a relation between Entity and Resources. Entities are subject of my code (for example people) and resources are things used by entities (for example documents). To map the relation I use a cross join table EntityResource and the Resource table.

When I need to refresh the resources associated to an Entity I delete all record form the cross join table (ResourceEntity) for a given Entity, then scan the Entity and search to see if for each Resource in the Entity if there is already an item in Resource table (resources may be shared among many entities). If the Resource exist I just recreate the relation, otherwise first I create the Resource and then the relation. Finally I delete all Resource that have no Entity association through the EntityResource cross join table.

My problem arises in the phase of association rebuilding, when I scan an Entity to re-associate existing Resource

foreach (var candidareResource in candidateResources) //set of resources for a given entity
{
    tbl_res_ResourcesEntity relation = new tbl_res_ResourcesEntity() //cross join table rebuild from a specific entity_id (already deleted here i just rebuild it)
    {
        ENTITY_ID       = entity.Id,
        ENTITY_TYPE_ID  = (int)entity.EType,
        ID_LANG         = candidareResource.ID_LANG,
        //navigation to Resource (1,1) relation is NULL by now
    };

    repository.Create(relation);
    //create is almost context.tbl_res_ResourcesEntities.InsertOnSubmit(relation);

    context.SubmitChanges();

    repository.AttachResource(relation, candidareResource);
    context.SubmitChanges();
} 

AttachResource basically check that the resource I try to create does not exist, if it exists create only association, otherwise create the resource and then the association, here the code:

public virtual tbl_res_ResourcesEntity AttachResource (tbl_res_ResourcesEntity objToUpdate, tbl_res_Resource resourceToAttach)
{
    base.Update(objToUpdate);

    if ( objToUpdate.ID <= 0)
        throw new ExtendedArgumentException("resourceToAttach", "No valid resource");

    var dbObjectToUpdate = this.Datacontext.tbl_res_ResourcesEntities.FirstOrDefault(res => res.ID == objToUpdate.ID);
    var dbResource = this.Datacontext.tbl_res_Resources.FirstOrDefault(res => res.RESOURCE_KEY == resourceToAttach.RESOURCE_KEY && res.RESOURCE_TYPE == resourceToAttach.RESOURCE_TYPE);

    
    if (dbResource == null) //always false in my testing scenario
    {
        dbResource = resourceToAttach;
        dbResource.SanifyLinqEntity();
        
        this.Datacontext.tbl_res_Resources.InsertOnSubmit(dbResource);
    }

    dbObjectToUpdate.Resource = dbResource;

    return dbObjectToUpdate;
}

In my scenario the Resources always exist so ignore the (dbResource == null)

Here is how the ORM show entity relation :

The issue arise when the same resource, is used many times by an Entity. This may happen if I would attach a document (Eg : a pdf file) twice. It's a bit strange, but document are attached in a language, so if entity request it in a specific language I have localization. But sometimes you don't have a localized document (eg : a PDF in English meant to be viewed also for the French language) and so the same resource end up to be attached twice. This is not an issue because document is stored only once, the "link" is doubled

So I get my PDF doubled

Resource

ID	ID_LANG	RES_KEY	RES_TYPE
1	en	document.pdf	pdf

ResourceEntity (cross join table)

ID	ENTITY_ID	ENTITY_TYPE	RESOURCE_ID	RESOURCE_TYPE	ID_LANG
1	1	Person	1	pdf	en
2	1	Person	1	pdf	fr

Entity

ID	NAME	TYPE
1	John Smith	Person

Problem is, if I try to attach the same Resource more than once (to the same entity), I got an error:

An attempt was made to remove a relationship between a tbl_res_Resource and a tbl_res_ResourcesEntity. However, one of the relationship's foreign keys (tbl_res_ResourcesEntity.RESOURCE_ID, tbl_res_ResourcesEntity.RESOURCE_TYPE_ID) cannot be set to null.

Not clear why I am seeing that behaviour.