CODEWITHSUNDEEP
c#sql-serverlinqlinq-to-sqlentity
Julian B
Julian B

Reputation: 1

Using LINQ to Entities in Insert

I am refactoring my Database from SQL Queries to Linq.

This function works fine:

public void WriteMessageWithSqlServerTimeOld(string message, string connectionString)
{
    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (var command = conn.CreateCommand())
        {
            SqlCommandBuilder builder = new SqlCommandBuilder();
            command.CommandText = $"INSERT INTO messages VALUES (GETDATE(), @message);";
            command.Parameters.AddWithValue("@message", message);
            command.ExecuteNonQuery();
        }
    }
}

But i would like to know how you could write this with linq. My naive attempt looked like this:

public void WriteMessageWithSqlServerTimeNew(string message, string connectionString)
{
    var db = new DataContext(connectionString);
    var messages = db.GetTable<MessageEntityClass>();
    var message = new MessageEntityClass()
    {
        Message = message, ServerTime = System.Data.Objects.SqlClient.SqlFunctions.GetDate()
    };
    messages.InsertOnSubmit(message);
    db.SubmitChanges();
}

But this gives me a

System.NotSupportedException: 'This function can only be called from 'LINQ to Entities'.'

Essentially my Question is, how can I call System.Data.Objects.SqlClient.SqlFunctions.GetDate() (or any other sql function) When inserting data into the database?

Upvotes: 0

Views: 143

Answers (1)

james
james

Reputation: 743

You should use the basic .NET method, DateTime.Now.

It translates to SQL as GetDate().

Your code : 

var message = new MessageEntityClass()
{
    Message = message, ServerTime = DateTime.Now
};

Upvotes: 1

Related Questions