动态SQL到LINQ实体框架

本文关键字：实体 框架 LINQ SQL 动态 | 更新日期: 2023-09-27 18:09:41

我只掌握了LINQ的基本知识。我会说SQL和JDBC，负责将动态PL/SQL转换为LINQ实体框架。如何在LINQ查询中添加条件WHERE子句?下面是一个非常简化的示例(省略类型info): 

Q1 := 'SELECT bDay  = b.birthday
            address = b.address
       FROM (' ;
Q2 := 'SELECT folks.birthday, Address.address
       FROM folks,
          (SELECT state,
                  surname AS name
           FROM Individuals, Addresses
           WHERE Individuals.addrId = Address.id
             AND Addresses.state = 'CA' ) find1
          ,(SELECT state,
                  surname AS name
           FROM Individuals, Addresses
           WHERE Individuals.addrId = Address.id
             AND Addresses.state = 'NV' ) find2
    AND find1.state(+) = folks.state';
IF input_parm = 'RED' THEN
   condAddOn :=condAddOn || 'AND folks.vacationHouse IS NOT NULL';
END IF;
IF input_parm = 'BLUE' THEN
   condAddOn :=condAddOn || 'AND folks.vacationHouse = 'Cabin';
END IF;
...
OPEN curs FOR Q1 || Q2 || condAddOn ')b';

试图找出c#/LINQ语法，这是我的尝试(自下而上):

var find1 = (from addr in Addresses
             from indiv in Individuals
             where indiv.addrId = addr.addrID
             select new
             {
                indiv.state,
                indiv.surname
             });
var find1OuterJoin = (from person in folks
                      join f1 in find1 on person.addrId equals f1.addrID
                      from f1OutJn in temp.DefaultIfEmpty()
                      select new
                      {
                         f1OutJn.state,
                         f1OutJn.surname
                      });
var Q2 = (from person in folks
          from addr in addresses
          from f1 in find1OuterJoin
          where person.addrId == addr.addrId
          && f1.state == folks.state
          select new
          {
            person.birthday
           ,addr.address
          });
var Q1 = (from q2 in Q1
          select new
          {bDay  = b.birthday
           ,address = b.address
         });

我不知道1)如果我正确地将Q1引入Q22)如何引入动态WHERE子句以等效的游标语句结束:

OPEN curs FOR Q1 || Q2 || condAddOn ')b';

添加:我可以使用函数或表达式来包含动态位吗?我看到了对Expression和Expandable()的引用，但不确定。添加:我尝试在LINQ查询

动态SQL到LINQ实体框架

当使用带有link的where方法时，您将返回一个IQueryable对象。这不会立即执行语句，因此可以执行以下操作:

var results = from person in folks
              join addr in addresses
              where person.addrId == addr.addrId
              select new { 
                  person.birthday, 
                  addr.address
                  };
 if(predicate){
    results = from r in results
               where /* new condition here */
               select r;
 }
 var resultSet = results.ToList().AsEnumerable();

对于其他链接操作符，特别是在使用lambda linq时，也可以使用AsQuerably扩展方法。

包括但不限于:

var results = folks.join(address, 
                 person => person.addrId,
                 addr => addr.addrId
                 (person, addr) => new {
                                          person.birthday,
                                          addr.address
                                       }).AsQueryable();
if(predicate)
{ 
   results = results.where(/*predicate for the where condition*/);
}
var resultSet = results.ToList().AsEnumerable();
相关文章：