Using LINQ to Entities in Insert

Question

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?

james · Accepted Answer · 2018-04-10 12:33:53Z

1

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
};

answered Apr 10, 2018 at 12:33

james

7433 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Julian B Over a year ago

Thanks, but that does not achieve what i intended. I want the datetime of the sql-server, this gives me the datetime of the client.

Collectives™ on Stack Overflow

Using LINQ to Entities in Insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related