How to do free (non-associated) join for one specific row with XPO?

Question

let's say I have a pesistent class called DailyVisitorSummary, which describes for each web page how many visitors it had per day. For simplicitzy, assume that we represent the day as a pure integer.

Now I would like to create a query to retrieve a specific day data, and also the data from its previous and next days. What I know is that there surely wil be at most one previous and next day data record for the same webpage, so I could write an SQL query (MySQL syntax) like:

SELECT c.*,p.*,n.* from DailyVisitorSummary c
LEFT JOIN DailyVisitorSummary p ON p.WebPage = c.WebPage AND p.Day = c.Day - 1
LEFT JOIN DailyVisitorSummary n ON n.WebPage = c.WebPage AND n.Day = c.Day + 1
WHERE c.Day = 453;

I would like to populate the following viewmodel with the result:

public class VMDailyVisitors3Day {
   public VMDailyVisitors CurrentDay { get; set; }
   public VMDailyVisitors PreviousDay { get; set; }
   public VMDailyVisitors NextDay { get; set; }
}

public class VMDailyVisitors {
    public int Day { get; set;; }
    public int WebPageID { get; set; }
    public int VisitorCount { get; set; }
}

How could I do this query with Linq to XPO? I need a LINQ solution, because I need to use the result in a server-mode MVC GridView.

Answer 1

I've found a workaround with the help of the DX support by using the PersistentAliasAttribute as a middle step, where I can do free joins, and then use that property in the XPQuery. If anyone intrested, check out here.