7

I have this model:

public class Teacher
{
    public int TeacherID { get; set; }
    public string Name { get; set: }
    public string Surname{ get; set; }
}

and when Model First run, it creates my Teachers table and DbSet, but for Name and Surname (which are string) it assigns NCLOB type to the column. Now, with NCLOB type I cannot do some operations like equals or distincts on my table....

How can I force MF to set columntype to varchar?

haim770
  • 48,394
  • 7
  • 105
  • 133
nicecatch
  • 1,687
  • 2
  • 21
  • 38

2 Answers2

7

I've managed to solve the issue setting the maximum string lenght into the model

public class Teacher
{
    public int TeacherID { get; set; }

    [StringLength(255, MinimumLength = 3, ErrorMessage = "My Error Message")]
    public string Name { get; set: }

    [StringLength(255, MinimumLength = 3, ErrorMessage = "My Error Message")]
    public string Surname{ get; set; }
}

Without the StringLength Orcale creates a NCLOB field that can contain up to 4Gb of data.

Note: Maximum lenght for varchar is 4000 bytes, so we cannot set more than 2000 as MaximumLenght (2 byte per character with Unicode)

nicecatch
  • 1,687
  • 2
  • 21
  • 38
  • I have an int with a .ToString() in a LINQ query and it is using TO_NCLOB in the query that is created. Is there there any way of preventing that? – JonathanPeel May 05 '17 at 15:55
  • @JonathanPeel If the int is available outside of the LINQ query, you could convert the int to a local string variable outside of LINQ, and use the string in the LINQ query. – mike mckechnie May 11 '17 at 10:01
  • I found a way to map extentions methods to Oracle functions, using `[Function(FunctionType.BuiltInFunction, "TO_CHAR")]`. I then created an `int.ToChar()`, and it works wonderfully. – JonathanPeel May 11 '17 at 10:14
  • @JonathanPeel How did you get this work? I can't seem to. I am using database first and have a generated edmx file. This is the error: "The specified method 'System.String ToChar(Int64)' on the type 'Acorn.Data.OracleFunctions' cannot be translated into a LINQ to Entities store expression."" – dannie.f May 12 '17 at 21:55
  • I am using code first, on an existing DB. `OracleFunctions` is a static class, with members like `[Function(FunctionType.BuiltInFunction, "TO_CHAR")] public static string ToChar(this int value) => Function.CallNotSupported();` In the DbContext class There is a method called `OnModelCreating`. There is a line of code in there: `modelBuilder.Conventions.Add(new FunctionConvention(typeof(OracleFunctions)));` – JonathanPeel May 13 '17 at 07:01
  • With DB first, the code files are _generated by a tool_, so it is not a good idea to change them. The DbContext class might be `partial`. If it is, you can create another `partial class` with the same name in a different file. This then won't be overridden if you update the edmx. – JonathanPeel May 13 '17 at 07:03
  • If you don't come right, let me know, and I will send you a sample. It is difficult explaining in comments. – JonathanPeel May 13 '17 at 07:03
  • 1
    @JonathanPeel I didn't realize that library only worked with Code First. Converted to Code First and added the FunctionConvention in OnModelCreating and it now works. Thanks. – dannie.f May 13 '17 at 09:03
5

Try to configure it explicitly:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Teacher>().Property(x => x.Name).HasColumnType("varchar");
    modelBuilder.Entity<Teacher>().Property(x => x.Surname).HasColumnType("varchar");
}

See Documentation

haim770
  • 48,394
  • 7
  • 105
  • 133