How to convert this linq code to inline sql

Question

How would I hide this query for inline sql or stored procedure?

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);
}

Once the inline query has been executed, how would I put the information into a user class? PostComments is a subquery - so how can I use ADO.NET to put it in a custom class?

Answer 1

Brief explanation

It seems that part of your question, which may seem complicated, is how to populate the custom class in the same way as the LINQ to SQL query ("L2S" from here in the output) for the anonymous class.

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>, , PostType, post_id. , PostIds, . .

, 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 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.

T-SQL , DataContext:

• DataContext.GetCommand()
• DataContext.Log

: , . SQL , , , . , SQL , , . , .

- 2.

DataContext.GetCommand():

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

, , Immediate - (Console.WriteLine ..).

DataContext.Log:

context.Log = Console.Out;

, , SQL-, . . , Debug, :

• Damien Guard: LINQ to SQL log to debug window, file, memory
• 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 , , . , . (.. 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, ( /). , #/VB.NET( LINQ to Objects/XML) SQL- .

LINQPad, , :

, , .

---

, !:)

Answer 2

, PostComments, , , :

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

Sql Command Adapters . :

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

I use keywords for reading, but for the dbs you use, you may need to change this.

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
)

use DataReader to get data and just load it into a list with your custom classes