Last active
June 24, 2018 09:00
-
-
Save huybn5776/c49534405b7d14bc9a72a1c029d683a6 to your computer and use it in GitHub Desktop.
Entity Framework 如何指定Select時需使用DbFunction
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE UserTbl ( | |
| vchId VARCHAR2(20) NOT NULL, | |
| vchName VARCHAR2(20) NOT NULL, | |
| vchType VARCHAR2(20) NOT NULL, | |
| nNumber NUMBER NOT NULL, | |
| nCancelFlag NUMBER(1, 0) DEFAULT 0 NOT NULL, | |
| CONSTRAINT PK_UserTbl PRIMARY KEY (vchId) | |
| ); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| public class OracleDbContext : DbContext | |
| { | |
| public string ConnectionString { get; set; } | |
| public OracleDbContext(string connectionString) => ConnectionString = connectionString; | |
| protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) | |
| { | |
| var logger = new LoggerFactory(); | |
| logger.AddConsole(); | |
| logger.AddDebug(); | |
| optionsBuilder.UseLoggerFactory(logger); | |
| optionsBuilder.UseOracle(ConnectionString); | |
| base.OnConfiguring(optionsBuilder); | |
| } | |
| public DbSet<User> User { get; set; } | |
| protected override void OnModelCreating(ModelBuilder modelBuilder) | |
| { | |
| base.OnModelCreating(modelBuilder); | |
| // 這個方法是在Select完才由程式去轉換，不會影響到產出的SQL | |
| modelBuilder.Entity<User>() | |
| .Property(d => d.Name) | |
| .HasConversion(d => d.ToString(), d => Rawtohex(d)); | |
| modelBuilder.Entity<User>() | |
| .Property(u => u.Type) | |
| .HasConversion(n => n.ToString(), n => TO_NUMBER(n)); | |
| // 將table、column名稱改為大寫，以順利查到資料表 | |
| foreach (var entityType in modelBuilder.Model.GetEntityTypes()) | |
| { | |
| entityType.Relational().TableName = entityType.Relational().TableName.ToUpper(); | |
| foreach (var property in entityType.GetProperties()) | |
| property.Relational().ColumnName = property.Relational().ColumnName.ToUpper(); | |
| } | |
| } | |
| // DbFunction函式的內容實作無關結果，僅作為向EF註明說有這樣的函式 | |
| [DbFunction] | |
| public static int TO_NUMBER(string str) => 0; | |
| [DbFunction] | |
| public static int TO_NUMBER(int i) => 0; | |
| [DbFunction] | |
| public static string Rawtohex(string str) => ""; | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [Table("UserTbl")] | |
| public class User | |
| { | |
| [Column("vchId"), Key] | |
| public string Id { get; set; } | |
| [Column("vchName")] | |
| public string Name { get; set; } | |
| [Column("vchType")] | |
| public int Type { get; set; } | |
| [Column("nNumber")] | |
| public int Number { get; set; } | |
| [Column("nCancelFlag")] | |
| public bool CancelFlag { get; set; } | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| public class UserRepository | |
| { | |
| private OracleDbContext _dbContext; | |
| public UserRepository(OracleDbContext dbContext) | |
| { | |
| _dbContext = dbContext; | |
| } | |
| public User Get(string id) | |
| { | |
| var data = _dbContext.User | |
| .Where(u => u.Id == id) | |
| .Select(u => new User | |
| { | |
| Id = u.Id, | |
| Name = u.Name.Trim(), | |
| Type = OracleDbContext.TO_NUMBER(u.Type), | |
| Number = u.Number, | |
| CancelFlag = u.CancelFlag | |
| }); | |
| return data.FirstOrDefault(); | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment