I've broke my mind trying to write next MSSQL query using XPO:
SELECT Gate.Name, grp.maxDate as 'Latest pass', a.Type, a.ReceivedTime as 'Imported at'
FROM Access AS a INNER JOIN
(SELECT GateId, MAX(OpenTime) AS maxDate
FROM Access
GROUP BY GateId) AS grp ON a.GateId = grp.GateId AND a.OpenTime = grp.maxDate INNER JOIN
Gate ON a.GateId = Gate.ID
order by Gate.ID
My Access table has around 2 mill records, but it has only 40 different GateId's. I want to select a line for every gate that will look like:
GateName OpenTime Type Imported at
....................................................................................
Supergate 20/09/2013 1 21/09/2013
Ultragate 19/09/2013 0 22/09/2013
My Access class looks like this:
public partial class Access : XPLiteObject
{
Gate fGateId;
[Association(@"AccessReferencesGate")]
public Gate GateId
{
get { return fGateId; }
set { SetPropertyValue<Gate>("GateId", ref fGateId, value); }
}
DateTime fOpenTime;
public DateTime OpenTime
{
get { return fOpenTime; }
set { SetPropertyValue<DateTime>("OpenTime", ref fOpenTime, value); }
}
byte fType;
public byte Type
{
get { return fType; }
set { SetPropertyValue<byte>("Type", ref fType, value); }
}
DateTime fReceivedTime;
public DateTime ReceivedTime
{
get { return fReceivedTime; }
set { SetPropertyValue<DateTime>("ReceivedTime", ref fReceivedTime, value); }
}
int fID;
[Key(true)]
public int ID
{
get { return fID; }
set { SetPropertyValue<int>("ID", ref fID, value); }
}
public Access(Session session) : base(session) { }
}
My Gate class:
public partial class Gate : XPLiteObject
{
int fID;
[Key(true)]
public int ID
{
get { return fID; }
set { SetPropertyValue<int>("ID", ref fID, value); }
}
string fName;
[Size(20)]
public string Name
{
get { return fName; }
set { SetPropertyValue<string>("Name", ref fName, value); }
}
}
[Association(@"AccessReferencesGate", typeof(Access))]
public XPCollection<Access> AccessCollection { get { return GetCollection<Access>("AccessCollection"); } }
public Gate(Session session) : base(session) { }
}
Any answers, or even RTFM links will be appreciated!
Answer from DevExpress support center, that worked for me:
XPView view = new XPView(typeof(Access));
view.AddProperty("Name", "GateId.Name");
view.AddProperty("LatestPass", "[<Access>][GateId = ^.GateId].Max(OpenTime)");
view.AddProperty("Type", "Type");
view.AddProperty("ImportedAt", "ReceivedTime");
view.Criteria = CriteriaOperator.Parse("OpenTime = [<Access>][GateId = ^.GateId].Max(OpenTime)");