LINQ Equivalent of SQL

Question

I have to translate the following SQL Query into LINQ equivalent

SELECT 
    0 AS DOCID, 
    'All_Forms ' as PAGE, 
    0 AS PAGENUMBER

UNION 

SELECT 
    DOCID,
    (CAST(IsNull(CUSTOMPAGE,PAGENUMBER) AS VARCHAR(10)) +'. '+TITLE ) AS PAGE,
    PAGENUMBER FROM Medical_Reports 
WHERE
    PAPERSTYLE='Normal' 
    AND PAGENUMBER<>10000 
ORDER BY
    docid

How to translate the above into LINQ equivalents?

Answer 1

Assuming you've taken care of the union in your database and retrieve your data through a view, it could be this:

from reports in medicalReports
where reports.PaperStyle == "Normal"
&& reports.PageNumber != 10000
order by reports.DocId
select reports

Answer 2

var reps = from r in Medical_Reports
               where r.PaperStyle  == 'Normal' && r.PageNumber != 10000
               order by r.DocId
           select { DocId = r.DocId,
                    Page = ((string)(r.CustomPage == r.PageNumber) + ". " + r.Title,
                    PageNumber = r.PageNumber };

reps.Add({ DocId = 0, Page = "All_Forms ", PageNumber = 0 });

Note: completely untested code. No guarantees.

Answer 3

Tested this with a hastily-made table based on your SQL. I hope it's what you're looking for:

var firstItem = new List<dynamic>() { 
    new { DocId = 0, Page = "All_Forms ", PageNumber = 0}
};

var pages = (from p1 in firstItem
             select p1).Union(
             from p2 in MedicalReports
             where p2.PaperStyle == "Normal" &&
                    p2.PageNumber != 10000
             orderby p2.DocId
             select new
             {
                 DocId = p2.DocId,
                 Page = ((p2.CustomPage != null) ? p2.CustomPage : p2.PageNumber.ToString()) + ". " + p2.Title,
                 PageNumber = p2.PageNumber
             });

Answer 4

This is my mot-a-mot translation (close to Tomas' answer):

class Item
{
  public int DocId;
  public string Page;
  public int PageNumber;
}
List<Item> result = db.MedicalReports
  .Where(r => r.PaperStyle == "Normal" && r.PageNumber != 1000)
  .OrderBy(r => r.DocId)
  .Select(r => new Item() 
  { 
    DocId = r.DocId, 
    Page = (r.CustomPage ?? r.PageNumber) + ". " + r.Title, 
    PageNumber = 0
  })
  .ToList();
result.Insert(0, new Item() { Page = "All_Forms" });