Dapper insert Foreign Key Id with where clause

I have a couple models: User and ShiftWork:

public class User
{
    public long Id { get; set; }
    public string Username { get; set; }
    public string SSO { get; set; }
    public ShiftWork ShiftWork { get; set; }
}

public class ShiftWork
{
    public long Id { get; set; }
    public string ShiftName { get; set; }
}

I also have classes for selecting and inserting data. I have been able to find solutions for everything I have wanted to do so far, but I cannot seem to do the insert into User that I am trying to do.

I want to be able to insert ShiftWorkId into the User table, but I need to get the Id from the ShiftName that is in a Winform text box.

So far my efforts have basically looked like this:

public void CreateUser(User user)
{
    using (IDbConnection cnn = SimpleDBConnection())
    {
        string sql = "INSERT INTO USER (Username, SSO, ShiftWorkId) Values (@Username, @SSO, (select @Id from ShiftWork Where ShiftName in (select ShiftName from ShiftWork)))";
        cnn.Execute(sql, new { user.Username, user.SSO, user.ShiftWork.Id });
    }
}

I have also tried:

string sql = "INSERT INTO USER (Username, SSO, ShiftWorkId) Values (@Username, @SSO, (select @Id from ShiftWork Where ShiftName  = @ShiftName))";

However, I can't seem to figure out how I should add the parameters for ShiftName.

The textbox has a custom autocomplete source that is populated from the database:

public List<ShiftWork> GetShifts()
{
    using (IDbConnection cnn = SimpleDBConnection())
    {
        string sql = "SELECT * FROM ShiftWork";
        var output = cnn.Query<ShiftWork>(sql);
        return output.ToList();
    }
}

Which in the code behind looks like:

AutoCompleteStringCollection ac = new AutoCompleteStringCollection();
foreach (var s in _shiftWorkRepository.GetShifts())
{
    ac.Add(s.ShiftName);
}

tbShift.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
tbShift.AutoCompleteSource = AutoCompleteSource.CustomSource;
tbShift.AutoCompleteCustomSource = ac;

So far, everything I have tried regarding assigning the text from the text box to the user.shiftworkId is giving me errors. All help is greatly appreciated

Solution

There are actually numerous reasons why this could happen:

1) In both queries presented the '@' character is used before Id. @ character is used only when you provide a variable to a query and in the case variable cannot be resolved.

2) Correctness of the SQL statement.

3) USER - is a SQL keyword, to make it work as a table name square brackets should be used.

4) In call to the Execute - Shift Id is passed to a query. ShiftName should be passed to make it work.

Here is the option to be used in this case to pass valid parameters:

cnn.Execute(sql, new { 
    Username = "here goes your username", 
    SSO = "here goes SSO", 
    ShiftName = "shift name" 
});

And the query below (assuming you are using SQL Server):

INSERT INTO [USER] (Username, SSO, ShiftWorkId) 
SELECT @Username, @SSO, Id
FROM ShiftWork 
WHERE ShiftName = @ShiftName

Note: Generally it is not a good practice to write complicated SQL using Dapper ORM.

Making SQL in your Dapper queries nice and clean usually leads to better maintainability.