private static void Read() { var str = System.Configuration.ConfigurationManager.ConnectionStrings["OracleDbContext"].ConnectionString; using (var conn = new OracleConnection(str)) using (var cmd = conn.CreateCommand()) { conn.Open(); cmd.CommandText = @"SELECT * from YOURVIEW"; var reader = cmd.ExecuteReader(); while (reader.Read()) { var gg = reader["GG"]; Console.WriteLine(gg); } } }
Dapper
1 2 3 4 5 6 7 8 9 10 11
private static void DapperQuery() { string sql = "SELECT * FROM YOURVIEW WHERE ROWNUM <= 1";
var str = System.Configuration.ConfigurationManager.ConnectionStrings["OracleDbContext"].ConnectionString; using (var connection = new OracleConnection(str)) { var result = connection.Query(sql).FirstOrDefault(); Console.WriteLine(result); } }
[Table("YOURVIEW")] public class YOURVIEW { public string GG { get; set; } }
public class YOURVIEWConfiguration : EntityTypeConfiguration<YOURVIEW> { public YOURVIEWConfiguration() { this.HasKey(t => t.GG); this.ToTable("YOURVIEW"); } }
SELECT "c"."GG"AS"GG" FROM"LADISAI"."YOURVIEW""c" WHERE (ROWNUM <= (10) )
where in 的效能問題處理
如果要在 ef 用 sql 的 in 最直覺就是寫這樣 , 可是這樣 oracle 會產一堆亂七八糟的 code 資料筆數少的話效能還好 , 一多的話直接送你升天
1 2 3 4
var nos = GetNos(); var result = db.Prod.AsNoTracking() .Where( x => nos.Contains( x.Id ) ) .ToArray();
這裡是精隨所在! 需要使用 Union All 搭配 where in 來完成這個動作 要串 Union All 的原因是 oracle 用 where in 只允許 1000 筆 , 超過會噴 ORA-01795 , 效能可以參考 這篇 接著要讓資料分頁 , 每頁 1000 筆 , 如果小於等於 1000 筆則要用另外一個 function
private static string GetProdSqlGreaterThan1000( IEnumerable<string> nos ) { //計算有幾頁 var page = nos.Count() / 1000;
//取得餘數 var countMod = nos.Count() % 1000;
//加上餘數那頁 if( countMod > 0 ) page += 1;
//因為使用 in 的話 oracle 噴 ORA-01795 只允許 1000 筆 , 所以用 union all 避開這個問題 List<string> sqls = new List<string>(); for( int i = 0 ; i < page ; i++ ) { //處理餘數 string strPage = ""; if( i == page ) strPage = string.Join( ", ", nos.Skip( i * 1000 ).Take( countMod ).Select( x => $"'{x}'" ) ); else strPage = string.Join( ", ", nos.Skip( i * 1000 ).Take( 1000 ).Select( x => $"'{x}'" ) );
var sql = string.Format( @" SELECT ID AS Id , PRODNAME AS ProdName FROM Prod WHERE Id IN ({0})", strPage ); //Debug.WriteLine( sql ); sqls.Add( sql ); }
//把 sql 語法進行 union all 處理 string finalSql = ""; int count = 0; foreach( var sql in sqls ) { //最後一筆時不需要 union all if( sqls.Count - 1 == count ) finalSql += sql; else finalSql += sql + " union all";
count++; }
return finalSql; }
沒分頁的 function
1 2 3 4 5 6 7 8 9 10 11 12 13 14
private static string GetProdSqlLessThan1000( IEnumerable<string> nos ) { var start = 0; var end = nos.Count(); var strPage = string.Join( ", ", nos.Skip( start ).Take( end ).Select( x => $"'{x}'" ) ); var sql = string.Format( @" SELECT ID AS Id , PRODNAME AS ProdName FROM Prod WHERE Id IN ({0})", strPage ); //Debug.WriteLine( sql ); return sql; }
判斷資料比數看要用哪個 function
1 2 3 4 5 6 7 8
private static string GetProdUnionAllSql( IEnumerable<string> nos ) { //小於等於 1000 就不用分頁 if( nos.Count() <= 1000 ) return GetProdSqlLessThan1000( nos );
//大於 1000 使用 return GetProdSqlGreaterThan1000( nos ); }
最後這樣寫即可
1 2
var sql = GetProdUnionAllSql( nos ); var result = db.Prod.SqlQuery( sql ).AsNoTracking().ToArray();
