How to represent an sql exists() command in LINQ

Question

I have a SQL problem that I use over and over again, but now that I need to do stuff more in LINQ. How do I do this? Is there a site that converts your sql to linq?

conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
//query
command.Parameters.Add(new SqlParameter("@email", email));

//else
command.CommandText = "if exists(SELECT pk_email FROM MyTable WHERE pk_email = @email) begin " +
                "UPDATE MyTable SET last_login = getdate(), is_logged_in = 'true' WHERE pk_email = @email; " +
                "end else begin " +
                "INSERT INTO MyTable (pk_email, last_login, is_logged_in) VALUES (@email , getdate(), 'true'); " +
                "end";
command.ExecuteNonQuery();

Answer 1

You could do something like this

if(From emails In MyTable Where emails.pk_email == email).Any) {
    'Then update your data here
}
else {
     'Insert your data
}

If you need help with the inserts or the updates on the datacontext just drop a comment.

Answer 2

In Entity Framework I usually use the following extension-method to achieve what you want (see the comment for an example).

/// <summary>
    /// Updates and entity and save it to the database.
    /// If it doesn't exist it creates a new entity and saves it to the database.
    /// <example>
    ///     <code>
    ///         //Updates or inserts a row into Account. The inserted/updated row will have its AccountNumber set to "17".
    ///         var account = db.Accounts.InsertOrUpdate(a => a.ID == id, a => a.AccountNumber = "17");
    ///     </code>
    /// </example>
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    /// <param name="allEntities"></param>
    /// <param name="entityFilter"></param>
    /// <param name="propertySetter"></param>
    /// <returns></returns>
    public static TEntity InsertOrUpdate<TEntity>(this ObjectSet<TEntity> allEntities, Func<TEntity, bool> entityFilter,
                                                  Action<TEntity> propertySetter) where TEntity : class, new()
    {
        //First we use the entityValueMapper to search for an existing entity.
        var entity = allEntities.Where(entityFilter).FirstOrDefault();
        if (entity == null)
        {
            entity = new TEntity();
            allEntities.AddObject(entity);
        }
        propertySetter(entity);
        allEntities.Context.SaveChanges();
        return entity;
    }