How do i convert this linq code to inline sql

Question

How would I covert this query to inline sql or a 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 in a custom class? PostComments is a subquery -- so how would I use ADO.NET to put it in a custom class?

Answer 1

If you mean that there is a relation between Posts and PostComments tables and there are repeated columns in both tables and one comment could be related to more than one post so you can easily create two commands:

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

and then execute them using Sql Command Adapters on two data tables. and then:

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

---

If this is not your case, so could you try to clarify your database structure?

Answer 2

I can't test this but something along the lines of:

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 capitalize keywords for readability but for the dbs you're using you might need to change that.

Answer 3

Use SQL Profiler to catch the generated query. Copy to new stored procedure and repair input parameters. Create (save) and use it :)

Answer 4

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 a DataReader to get the data & just load it in a list with your custom classes