我如何将此 linq 代码转换为内联 sql

Question

我如何将此查询转换为内联 sql 或存储过程？

var a = from arow in context.post
where arow.post_id == id && arow.post_isdeleted == false
select new
{
     arow.post_id,
     PostComments = from c in context.comment
                    where c.CommentPostID == arow.post_id
                    select new
                    {
                        c.id,
                        c.title     
                    }
}


List<PostType> pt;
foreach (var s in a)
{
     pt = new PostType();
     pt.PostID = s.post_id;

     //how would I use ADO.NET to put this in a custom class?
     foreach(var ctl in s.PostComments)
     {
         ctl.Title = ctl.title;
         pt.CommentT.Add(ctl);
     }
     ptl.Add(pt);
}

执行内联查询后，如何将信息放入自定义类中？ PostComments 是一个子查询 - 那么我如何使用 ADO.NET 将其放入自定义类中？

Answer 1

简短说明

您的问题中看似棘手的部分似乎是如何以 LINQ to SQL（从这里开始的“L2S”）查询对匿名进行的相同方式填充自定义类班级。

根据您的 foreach 循环，我猜测您的自定义类与这些类似：

public class PostType
{
    public int PostId { get; set; }
    public List<PostComment> PostComments { get; set; }
}
public class PostComment
{
    public int CommentId { get; set; }
    public string Title { get; set; }
}

LINQ 查询应该相当于此 T-SQL 语句：

SELECT P.post_id, C.id, C.title 
FROM post As P, comment As C
WHERE
    P.post_id = @PostId
    AND P.post_isdeleted = 0  -- 0 is false
    AND C.CommentPostID = P.post_id

与 L2S 版本不同（有关详细信息，请参阅下面的详细说明部分），此语句返回平展结果，每行包含 P.post_id、C.id 和 C.title。如果您的 PostType 类以相同的方式表示一个条目，那么这将很容易解决（我并不提倡这样的设计；我只是评论该设计如何改变它的填充方式） 。类中的层次关系改变了事情。

此外，您的代码显示了一个 List，但列表不是必需的，因为总会有一个 PostType，因为您要根据 post_id。如果删除该条件，那么您可能会在满足其他条件的情况下获得具有不同 PostId 的多个匹配项。如果是这种情况，则需要更改下面的代码。

也就是说，让我们进入一些 ADO.NET 并使用 SqlDataReader 填充类。

int postIdInput = 42; // desired post_id to search for

// PostType delcared prior to getting the results
PostType postType = new PostType()
{
    PostId = postIdInput,
    PostComments = new List<PostComment>()
};

 // Database interaction starts here...
 // updated SQL statement to use column name aliases for clarity when used by the SqlDataReader
 string sqlStatement = @"SELECT P.post_id As PostId, C.id As CommentId, C.title As Title
                         FROM post As P, comment As C
                         WHERE
                             P.post_id = @PostId
                             AND P.post_isdeleted = 0  -- 0 is false
                             AND C.CommentPostID = P.post_id";

 string sqlConnectionString = "..."; // whatever your connection is... probably identical to your L2S context.Connection.ConnectionString
 using (SqlConnection conn = new SqlConnection(sqlConnectionString))
 {
     conn.Open();
     SqlCommand command = new SqlCommand(sqlStatement, conn);
     command.Parameters.AddWithValue("@PostId", postIdInput); // use Parameters.Add() for greater specificity

    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        // postId was set based on input, but could be set here as well although it would occur repeatedly
        // if desired, uncomment the 2 lines below and there's no need to initialize it earlier (it'll be overwritten anyway)
        //int postId = Int32.Parse(reader["PostId"].ToString());
        //postType.PostId = postId;
        int commentId = Int32.Parse(reader["CommentId"].ToString());
        string title = reader["Title"].ToString();

        // add new PostComment to the list
        PostComment postComment = new PostComment
        {
            CommentId = commentId,
            Title = title
        };
        postType.PostComments.Add(postComment);
    }

    // done! postType is populated...
}

// use postType...

这应该涵盖你的场景。但是，要获得更详细的答案，请继续阅读！

---

详细解释（又名“授人以鱼……”）

假设您不知道如何获得等效的 SQL 语句。虽然有不同的方法可以做到这一点，但我将重点关注您正在使用 L2S 的事实并探索一些相关选项。

第 1 步：将 LINQ 查询转换为 SQL （通过“作弊”）

您很幸运，因为有一条捷径。将现有的 LINQ 表达式转换为 SQL 比向后将 SQL 转换为 LINQ 稍微方便一些。

您可以使用以下任一 DataContext 选项从代码中获取翻译后的 T-SQL 语句：

• DataContext.GetCommand() 方法
• DataContext.Log 属性

注意： 我确实说过这是一个快捷方式。了解 SQL 知识是有好处的，需要明确的是，我并不是建议盲目使用生成的输出。诚然，SQL 有时可能与您的预期有所不同，尽管如此，它提供了一个不错的起点。如果需要，您可以对其进行调整。

使用这两种方法之一并复制结果 - 您将在第 2 步中需要它。

DataContext.GetCommand() 用法示例：

var query = /* your L2S query here */;
string sqlStatement = context.GetCommand(query).CommandText;    // voila!

要获取结果，请设置断点并复制其值，在立即窗口中检查它，或将其显示在某处（Console.WriteLine 等）。

DataContext.Log 用法示例：

context.Log = Console.Out;

在该上下文上执行的查询会将其 SQL 语句转储到控制台窗口。您可以从那里复制它。要将它们转储到其他地方，例如调试输出窗口，请查看以下链接：

• Damien Guard 的博客文章：用于调试窗口、文件、内存或多个写入器的 LINQ to SQL 日志
• 将 LINQ To SQL 日志发送到调试器输出窗口

步骤2：有了 SQL 语句，就可以在 ADO.NET 中使用它了。

现在您已经有了 SQL 语句，我们可以在 ADO.NET 中使用它了。当然，您也可以使用存储过程，并且替换它应该不难。

不过，在使用它之前，您可能需要清理该语句。我在本地使用了类似的查询来获取此信息，您生成的语句可能类似于：

SELECT [t0].[post_id], [t1].[id], [t1].[title], (
 SELECT COUNT(*)
 FROM [comment] AS [t2]
 WHERE [t2].[id] = [t0].[post_id]
 ) As [value]
FROM [post] As [t0]
LEFT OUTER JOIN [comment] As [t1] ON [t1].[CommentPostID] = [t0].[post_id]
WHERE ([t0].[post_id] = @p0) AND ([t0].[post_isdeleted] = 0)
ORDER BY [t0].[post_id], [t1].[id]

注意到嵌入的 SELECT COUNT(*) 吗？ L2S 查询从未请求计数，但结果请求连接上使用的相等 ID 的计数。另请注意，列没有别名。您可以根据列的实际名称来引用这些列（即 post_id 与 PostId）。此外，SQL 参数被命名为@p0...@pn 并应用默认排序顺序。您可以将其复制/粘贴到之前使用的 SqlDataReader 中，但您需要重命名列和参数以匹配。

下面再现了上述内容的清理版本，其中重命名了参数，并注释掉了不必要的部分（如果采用此方法，请对其进行测试以确保其与预期相同）：

SELECT [P].[post_id] As PostId, [C].[id] As CommentId, [C].[title] As Title--, (
-- SELECT COUNT(*)
-- FROM [comment] AS [t2]
-- WHERE [t2].[id] = [t0].[post_id]
-- ) As [value]
FROM [post] As [P]
LEFT OUTER JOIN [comment] As [C] ON [C].[CommentPostID] = [P].[post_id]
WHERE ([P].[post_id] = @PostId) AND ([P].[post_isdeleted] = 0)
--ORDER BY [t0].[post_id], [t1].[id]

现在可以将上述内容与之前的 SqlDataReader 一起使用。

如果 L2S 查询采用 SelectMany，例如：

var query = from arow in context.post
            from c in context.comment
            where arow.post_id == id && arow.post_isdeleted == false
                  && c.CommentPostID == arow.post_id
            select new
            {
                arow.post_id,
                c.id,
                c.title    
            };

SelectMany L2S 查询会生成类似于以下内容的 SQL 语句：

SELECT [t0].[post_id], [t1].[id], [t1].[title]
FROM [post] As [t0], [comment] As [t1]
WHERE ([t0].[post_id] = @p0) AND ([t0].[post_isdeleted] = 0)
      AND ([t1].[CommentPostID] = [t0].[post_id])

LINQPad

虽然此详细说明可能看起来令人难以理解，但有一种简单的方法可以让您轻松获得此信息。如果您还没有尝试过 LINQPad 那么我强烈推荐它 - 它也是免费的！ LINQPad 将向您显示 L2S 查询的结果，有一个 SQL 选项卡来查看生成的 SQL，并且还显示使用的 lambda 表达式（上面的查询语法显示为 lambda/扩展等效项）。最重要的是，它是通用 C#/VB.NET（包括 LINQ to Objects/XML）以及具有数据库支持的 SQL 编码等的出色工具。

下面是 LINQPad 的小屏幕截图，显示了之前讨论的一些主题：

我不想占用更多实际页面财产比我已经拥有的所以单击此处查看原始尺寸的图像。

---

如果您已经做到了这一步，那么恭喜您！ :)

Answer 2

如果您的意思是 Posts 和 PostComments 表之间存在关系，并且两个表中都有重复的列，并且一条评论可能与多个帖子相关，因此
您可以轻松创建两个命令：

-Select * from Posts where post_Id = id AND IsDeleted = 0;
-Select * from Postcomments where id = cid;

然后在两个数据表上使用 Sql 命令适配器执行它们。然后：

foreach(DataRow dr in PostsTable.Rows)
{
 //Fill the Post Custom class
 SecondTable.DefaultView.RowFilter = string.Format("PostID = {0}",dr["postID"]);
 foreach(DataRow r in SecondTable.Rows)
 {
  //Fill the Comments Custom class
 } 
}

---

如果这不是您的情况，那么您可以尝试澄清您的数据库结构吗？

Answer 3

使用 SQL Profiler 捕获生成的查询。复制到新的存储过程并修复输入参数。创建（保存）并使用它:)

Answer 4

我无法测试这一点，但大致如下：

SELECT 
    p.post_id
    c.id,
    c.title
FROM 
    post p 
WHERE 
    p.id == 'id' and 
    isdeleted = false
INNER JOIN comment c ON c.commentpostid = p.post_id

我将关键字大写以提高可读性，但对于您正在使用的数据库，您可能需要更改它。

Answer 5

select post_id, id, title from postcomments pc
where post_id = @id and exists(
    select post_id form post p where p.post_id = pc.post_id and isdeleted = false
)

使用 DataReader 获取数据&只需将其与您的自定义类一起加载到列表中