Linq to Sql problem

Question

I have 2 tables Users and Queries. They are connected via FK(UserId) in Queries table. I need to add queries added, for example, by user with login "Bob" to all users.

Here is a chunk of code i'm using:

    public bool SaveUserQuery(string userName, Query query) {

        var db = new UserDataClassesDataContext();

        Table<User> users = db.Users;
        if ( userName.ToLower() == "bob" ) {
            foreach ( var user in users ) {
                var tempQuery = new Query();
                tempQuery.Name = query.Name;
                tempQuery.FolderName = query.FolderName;
                tempQuery.Layout = query.Layout;
                tempQuery.Description = query.Description;                    
                tempQuery.Query1 = query.Query1;
                tempQuery.UserID = user.UserId;

                try {
                    user.Queries.Add(q); 
                }              
                catch (Exception e) {

                    Logger.Log.Error("attach", e);
                }                    
            }
            db.SubmitChanges();
            return true;
        }
    }

It throws error when adding: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Queries_Users". The conflict occurred in database "OLAPUsers", table "dbo.Users", column 'UserId'.

How can i fix this or archive the goal i have?

Answer 1

Make sure that your primary keys are setup correctly in the DB schema. You can query tables without primary keys, but you can't do inserts unless everything is setup correctly, and the data context's view of the DB is current.

Answer 2

Try this:

public bool SaveUserQuery(string userName, Query query)
{
   var db = new DataContext();
   if ( userName.ToLower() == "bob" ) 
   {
      List<Query> queries = new List<Query>();
      foreach ( var user in db.GetTable<Users>()) 
      {
         Query tempQuery = new Query(query.Name, query.FolderName, query.Layout,  query.Description, query.Query1, user.UserId);
         //and ofc create this constructor
         queries.Add(tempQuery);             
       }
       db.GetTable<Query>().InsertAllOnSubmit(queries);
       db.SubmitChanges();
       return true;
    }
}