com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for ...


My entity class:



```
 @Entity
    @Table(catalog = "emp", name = "person")
    @XmlAccessorType(XmlAccessType.FIELD)
    public class Person implements Serializable {
    private static final long serialVersionUID = 1L;

    /**
     */

    @Column(name = "ID", nullable = false,length = 50)
    @Basic(fetch = FetchType.EAGER)
    @Id
    @XmlElement
    String id;
    /**
     */

    @Column(name = "ADDRESS", length = 50)
    @Basic(fetch = FetchType.EAGER)
    @XmlElement
    String address;
    /**
     */

    @Column(name = "EMAIL", length = 50)
    @Basic(fetch = FetchType.EAGER)
    @XmlElement
    String email;



    @Column(name = "DATE")
    @Basic(fetch = FetchType.EAGER)
    @XmlElement
    DateTime date;
    /**
     */

    /*getters and setters */

}

```

I'm using joda time for my date. When I persist, it throws this exception:



```
> Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'DATE' at row 1

```

My Date field contain this value : 2015-09-02T16:24:05.226+04:00


Below is the code where it persist in db:



```
public Person save(Person person) {

        Person currentPerson = entityManager.find(Person.class,
                person.getId());
        if (currentPerson != null) {
            entityManager.detach(currentPerson);
            person =entityManager.merge(person);
        } else {
            entityManager.persist(person);
        }
        return person;
    }

```

I am using hibernate/jpa and mysql. My database is created base on my entity class. my date is of type datetime in my entity class, but in database it is of type tinyblob.


How to solve this?
and it's done exactly what you told it to: generate a field that's capable of holding an SQL DATE. Which means a field without a time.  

The correct way of indicating a timestamp using JPA is as follows:



```
   /**
     * Time/date of creation of an entity.
     */
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "CREATION_DATE")
    protected Date creationDate;

```
@kevin\_terschlus 
could you try running the following command against your service management db and then restarting the cache builder please?

truncate table lastmodified

thanks,
shane You are mixing [expressions](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.expression?view=netframework-4.7.1) with [lambda expressions](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.lambdaexpression?view=netframework-4.7.1). There are many posts showing how you can combine lambda expressions, but the essential part is to compose expressions from lambda expression [bodies](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.lambdaexpression.body?view=netframework-4.7.1) and rebind the [parameters](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.lambdaexpression.parameters?view=netframework-4.7.1).


The later is usually achieved by a custom [`ExpressionVisitor`](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.expressionvisitor?view=netframework-4.7.1) like this:



```
using System.Linq.Expressions;

public static class ExpressionExtensions
{
    public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
    {
        return new ParameterReplacer { Source = source, Target = target }.Visit(expression);
    }

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression Source;
        public Expression Target;
        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node == Source ? Target : base.VisitParameter(node);
        }
    }
}

```

Now regarding the EF Core combined query filters. 


Using dictionaries and expression lists seems overcomplicated for what are you doing. Since [`IMutableEntityType`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.imutableentitytype?view=efcore-2.1) provides read/write access to the [`QueryFilter`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.imutableentitytype.queryfilter?view=efcore-2.1#Microsoft_EntityFrameworkCore_Metadata_IMutableEntityType_QueryFilter), the same can be achieved with a small set of a custom extension methods.


All they go inside a class like this:



```
using System;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public static class QueryFilterExtensions
{
}

```

First method:



```
public static void AddQueryFilter(this IMutableEntityType target, LambdaExpression filter)
{
    if (target.QueryFilter == null)
        target.QueryFilter = filter;
    else
    {
        var parameter = target.QueryFilter.Parameters[0];
        var left = target.QueryFilter.Body;
        var right = filter.Body.ReplaceParameter(filter.Parameters[0], parameter);
        var body = Expression.AndAlso(left, right);
        target.QueryFilter = Expression.Lambda(body, parameter);
    }
}

```

This is a non generic method which combines the exiting filter with passed filter using `AndAlso` (C# `&&`) operator and shows the aforementioned lambda expression combining principles.


However it's not so useful directly, like inside your entity type configuration loop (it can, but requires you to build manually the lambda expression instead of letting C# compiler do that). So here comes the second method:



```
public static void AddQueryFilter<T>(this IMutableEntityType target, Expression<Func<T, bool>> filter)
{
    LambdaExpression targetFilter = filter;
    if (target.ClrType != typeof(T))
    {
        var parameter = Expression.Parameter(target.ClrType, "e");
        var body = filter.Body.ReplaceParameter(filter.Parameters[0], parameter);
        targetFilter = Expression.Lambda(body, parameter);
    }
    target.AddQueryFilter(targetFilter);
}

```

It's a generic method - not quite type-safe, but allows you to use a compile time lambda expression and bind it to the actual entity type as follows:



```
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
    if (typeof(IDeleted).IsAssignableFrom(entityType.ClrType))
        entityType.AddQueryFilter<IDeleted>(e => e.DateTimeDeleted == null);
}

```

Looks better, isn't it :)


The last custom extension method is complement to (replacement of) the standard EF Core generic [`HasQueryFilter`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.builders.entitytypebuilder-1.hasqueryfilter?view=efcore-2.1#Microsoft_EntityFrameworkCore_Metadata_Builders_EntityTypeBuilder_1_HasQueryFilter_System_Linq_Expressions_Expression_System_Func__0_System_Boolean___) method:



```
public static EntityTypeBuilder<TEntity> AddQueryFilter<TEntity>(this EntityTypeBuilder<TEntity> target, Expression<Func<TEntity, bool>> filter)
    where TEntity : class
{
    target.Metadata.AddQueryFilter(filter);
    return target;
}

```

and allows you to replace



```
this.AddToDict<DbMyEntity>(t => t.Name == null || t.Name == "Something");

```

with the more convenient



```
modelBuilder.Entity<DbMyEntity>()
    .AddQueryFilter(t => t.Name == null || t.Name == "Something");

```

**Update (EF Core 3.0):** `QueryFilter` property has been replaced with `GetQueryFilter` and `SetQueryFilter` extension methods. Looks like YYMMDD to me. I would try to verify this in the documentation or whatever information source you have on the old database. You could also look for inappropriate numbers like MM not between 1 and 12 and DD not between 1 and 31. If you find any inappropriate numbers than this is the wrong approach. This does not look like a date based on an offset number of days after a start date because the start date would have to be about 2600 years ago. For anyone having similar problems, I have solved my issue. It was several-fold.


* As Darren suggested and Paul backed up, the Max..Size properties in the configuration needed to be enlarged. The SvcTraceViewer utility helped in determining this, but it still does not always give the most helpful error messages.
* It also appears that when the Service Reference is updated on the client side, the configuration will sometimes not update properly (e.g. Changing config values on the server will not always properly update on the client. I had to go in and change the Max..Size properties multiple times on both the client and server sides in the course of my debugging)
* For a DataTable to be serializable, it needs to be given a name. The default constructor does not give the table a name, so:



```
return new DataTable();

```

will not be serializable, while:



```
return new DataTable("someName");

```

will name the table whatever is passed as the parameter. 


Note that a table can be given a name at any time by assigning a string to the `TableName` property of the DataTable.



```
var table = new DataTable();
table.TableName = "someName";

```


Hopefully that will help someone. you might be able to use synchronize. if you have more than 2 tables, you will need to use it multiple times. As I understand, you are following the following approach:



```
modelBuilder.Entity<IdentityRole>().HasData(new IdentityRole { Name = "Admin", NormalizedName = "Admin".ToUpper() });

```

There are two problems with this code.


The first is the usage of `modelBuilder.Entity<IdentityRole>()`. This makes `IdentityRole` class to be treated by EF Core as an *entity*, hence the `IdentityRoles` table it tries to create. It's because `IdentityRole` class is *different* from what your model is using - `IdentityRole<Guid>`. Change it to `modelBuilder.Entity<IdentityRole<Guid>>()` (and also the `new` operator) and the problem will be solved.


The second problem is that `HasData` *requires* PK values to be specified, as explained in the [Data Seeding](https://docs.microsoft.com/en-us/ef/core/modeling/data-seeding) section of the EF Core documentation:



> 
> The primary key value needs to be specified even if it's usually generated by the database. It will be used to detect data changes between migrations.
> 
> 
> 


So additionally to changing the `HasData` generic type argument, you need to pre generate Guid to be used as Id for the new role (see [Entity Framework Core Auto Generated guid](https://stackoverflow.com/questions/53493025/entity-framework-core-auto-generated-guid/53495148#53495148) for more details a the similar issue) and use something like this:



```
modelBuilder.Entity<IdentityRole<Guid>>().HasData(
    new IdentityRole<Guid>
    {
        Id = new Guid("pre generated value"),
        Name = "Admin",
        NormalizedName = "Admin".ToUpper()
    }
);

``` If I am not wrong, you are trying to read or loop through the rows from datatable. This error states that the datasource(DataTable) is not having any rows. To avoid this exception use this before you read or loop through datatable:  


```
if(dt.Rows.Count  > 0 ){
     //Do your stuff here.
}
```
  

  

  

--Amit java.sql.timestamp is a subclass of java.util.date - it should work the same for comparison purposes:

<https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/timestamp.html>

if you specifically need a date, you could convert it back pretty easily; check if isinstance(java.sql.timestamp… Using [reflection](/questions/tagged/reflection "show questions tagged 'reflection'") and [expression-trees](/questions/tagged/expression-trees "show questions tagged 'expression-trees'") you can provide the parameters and then call `OrderBy` function, Instead of returning `Expression<Func<Task, T>>` and then calling `OrderBy`.


Note that `OrderBy` is an extension method and has implemented in both `System.Linq.Enumarable` and `System.Linq.Queryable` classes. The first one is for [linq-to-objects](/questions/tagged/linq-to-objects "show questions tagged 'linq-to-objects'") and the latter is for [linq-to-entities](/questions/tagged/linq-to-entities "show questions tagged 'linq-to-entities'"). [entity-framework](/questions/tagged/entity-framework "show questions tagged 'entity-framework'") needs the expression tree of the query in order to translate it to SQL commands. So we use the `Queryable` implementation.


It can be done by an extension method(explanations added as comments):



```
public static IOrderedQueryable<TSource> OrderBy<TSource>(
       this IQueryable<TSource> query, string propertyName)
{
    var entityType = typeof(TSource);

    //Create x=>x.PropName
    var propertyInfo = entityType.GetProperty(propertyName);
    ParameterExpression arg = Expression.Parameter(entityType, "x");
    MemberExpression property = Expression.Property(arg, propertyName);
    var selector = Expression.Lambda(property, new ParameterExpression[] { arg });

    //Get System.Linq.Queryable.OrderBy() method.
    var enumarableType = typeof(System.Linq.Queryable);
    var method = enumarableType.GetMethods()
         .Where(m => m.Name == "OrderBy" && m.IsGenericMethodDefinition)
         .Where(m =>
         {
            var parameters = m.GetParameters().ToList();
            //Put more restriction here to ensure selecting the right overload                
            return parameters.Count == 2;//overload that has 2 parameters
         }).Single();
    //The linq's OrderBy<TSource, TKey> has two generic types, which provided here
    MethodInfo genericMethod = method
         .MakeGenericMethod(entityType, propertyInfo.PropertyType);

    /*Call query.OrderBy(selector), with query and selector: x=> x.PropName
      Note that we pass the selector as Expression to the method and we don't compile it.
      By doing so EF can extract "order by" columns and generate SQL for it.*/
    var newQuery = (IOrderedQueryable<TSource>)genericMethod
         .Invoke(genericMethod, new object[] { query, selector });
    return newQuery;
}

```

Now you can call this overload of `OrderBy` like any other overload of it.  

For example:



```
var cheapestItems = _context.Items.OrderBy("Money").Take(10).ToList();

```

Which translates to:



```
SELECT TOP (10)  {coulmn names} FROM  [dbo].[Items] AS [Extent1] 
       ORDER BY [Extent1].[Money] ASC

```

This approach can be used to define all overloads of `OrderBy` and `OrderByDescending` methods to have `string` property selector. > 
> What attributes I have to attach to the CreatedDate and ModifiedDate properties to make them automatically populated by the server based on the above scenario?
> 
> 
> 


**Solution 1)**



```
namespace Joukyuu.Models
{
    public class Passage
    {
        public int PassageId { get; set; }
        public string Contents { get; set; }


        public DateTime CreatedDate { get; set; }
        public DateTime ModifiedDate { get; set; }

       public Passage()
       {          
         this.CreatedDate  = DateTime.UtcNow;
         this.ModifiedDate = DateTime.UtcNow;
       }
    }
}

```

and by edit you have to change/update it by your self!


**Solution 2)**


Custom attribute:



```
[SqlDefaultValue(DefaultValue = "getutcdate()")]
public DateTime CreatedDate { get; set; }

```

[Entity Framework 6 Code first Default value](https://stackoverflow.com/questions/19554050/entity-framework-6-code-first-default-value)


**Solution 3)**


with help of Computed:



```
[Required, DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime CreatedUtc { get; set; 


  "dbo.Products",
            c => new
                {
                    ProductId = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                    CreatedUtc = c.DateTime(nullable: false, defaultValueSql: "GETUTCDATE()"),
                })
            .PrimaryKey(t => t.ProductId);

```

<https://andy.mehalick.com/2014/02/06/ef6-adding-a-created-datetime-column-automatically-with-code-first-migrations/>


**Solution 4)**
You can also do this with command interceptor by modifying manually the query.


**Solution 5)** 
Use Repository pattern to manage the data creation and set it by CreateNew 
This is my favour Solution!


<https://msdn.microsoft.com/en-us/library/ff649690.aspx>


**Solution 6)** 
just set it or get in in the UI or in your VM.







In **Entity Framework Core 1.0** easy:



```
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Passage>()
        .Property(b => b.CreatedDate )
        .HasDefaultValueSql("getdate()");
}

```