C# Linq Select data from multiple tables

Question

In 1 query, I want to select results of 2 tables (Union).

Table 1: StationStatus

Table 2: DetectorStatus

I can excecute a query as followed:

SELECT TOP 1000 [DetectorStatusID],
      [DetectorID],
      [DateTime],
      [DetectorModeID],
      NULL as StationModeID,
      [Status]
  FROM [GMS_MAN].[dbo].[DetectorStatus] 
  UNION SELECT TOP 1000 [StationStatusID],
      [DetectorID],
      [DateTime],
      NULL as DetectorMOdeID,
      [StationModeID],
      [Status] FROM [GMS_MAN].[dbo].StationStatus 
ORDER BY Status

Which will result in the 2 tables combined in 1.

However, I don't know how to do this in the code.
The query I'm currently executing is only for the DetectorStatus Table

    [Query]
[OutputCache(OutputCacheLocation.Server, 60)]
public IQueryable<DetectorStatu> GetDatabacklog(int userID)
{
    User user = ObjectContext.Users
        .Include("TeamMemberships")
        .First(u => u.UserID == userID);

    var teamIDs = user.TeamMemberships.Select(t => t.TeamID).ToList();
    var siteIDs = ObjectContext.SiteMemberships.Where(t => teamIDs.Contains(t.TeamID)).Select(t => t.SiteID).ToList();
    var DetectorStatus =  ObjectContext.DetectorStatus
        .Include("Detector")
        .Include("Detector.Track")
        .Include("Detector.Site")
        .Include("Detector.EnabledDetectorTypes")
        .Include("Detector.EnabledDetectorTypes.DetectorType")
        .Where(d => siteIDs.Contains(d.Detector.SiteID))
        .OrderBy(d => d.Status);

    return DetectorStatus;
}

And I call it like:

private void GetDatabacklog()
{
    LoadOperation<DetectorStatu> loadOp = context.Load(context.GetDatabacklogQuery(App.CurrentUser.UserID), LoadBehavior.RefreshCurrent, LoadDatabacklogCompleted, null);
}

private void LoadDatabacklogCompleted(LoadOperation<DetectorStatu> loadOperation)
{
    //The rest of the code where i show the results.
}

My question, how can I use this in multiple tables?
Because in the query I need to call StationStatu and DetectorStatu, however I don't know how to do this.

When I add .Union to anything, I get the following error:

The code I'm using:

var detectorSt = ObjectContext.DetectorStatus.Select(x => new { x.DetectorID, x.Status});
var stationSt = ObjectContext.StationStatus.Select(x => new { x.DetectorID, x.Status}); 
var unioned = detectorSt.Union(stationSt);
return unioned;

.Include will only work in case of eager loading. And if there is some relationship between tables. Still if you want combined data, then perform a join. By looking at your table structure, DetectorId is common column to perform a join. — Pavvy, Feb 10 '17 at 10:17
Use anonymous types and `Union`: `var detectorSt = ObjectContext.DetectorStatus.Select(x => new { CommonPropertiesHere });: var stationSt = ObjectContext.StationStatus.Select(x => new { CommonPropertiesHere }); var unioned = detectorSt.Union(stationSt);` — Tim Schmelter, Feb 10 '17 at 10:20
You can use a Join. See msdn : https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — jdweng, Feb 10 '17 at 10:42
Maybe take union of `DetectorID` from both tables and then left-join the IDs with the tables? What do you want to do with `Status`? Looks like different values with same column names. — grek40, Feb 10 '17 at 10:55
@TimSchmelter I have tryed that option, but the **var unioned** always gives me the error: **System.Linq.Iqueryable does not contain a definition for Union** The DetectorID should by the way never be in both tables. However in my question it is. This is because the StationStatus table is gona be new. (DetectorID 1542) — Mitch, Feb 10 '17 at 11:05
@Mitch: could you also add the code before this line? You can't add Union to _anything_, both must be the same type — Tim Schmelter, Feb 10 '17 at 11:22
@TimSchmelter Made an edit. The Error is comming from the return statement now. — Mitch, Feb 10 '17 at 11:28
Can you explain, where in your result a join would happen? Because if the entries from both tables are totally distinct, you should definitely query them separately and possibly join them in your application in memory, but not server side. — grek40, Feb 10 '17 at 11:46
@Mitch: either create a new class as a common type for the `Union`-result, so the properties which both tables share, and return that (you need to change the retun type as well). Or create instances of `DetectorStatus` after the `Union`: `return detectorSt.Union(stationSt).Select(x => new DetectorStatus{ ... })` — Tim Schmelter, Feb 10 '17 at 11:49
I basically want the results of 2 tables, in 1 query. Later on the site i want to OrderBy Status. And if in table A, Status is 30-40 and in table B Status is: 34. I want it to go like 30-34-40 etc. — Mitch, Feb 10 '17 at 11:50

score 0 · Answer 1 · edited May 23 '17 at 12:17

I can't see any reason to query the data from two tables in a single query, without linking the results in any way. So I suggest you query the tables separately, select them into a common data object and merge them in client rather than server side (you can still sort the queries on server if you want, then just merge sorted data in client).

public class StatusTransferObject
{
    public int DetectorId { get; set; }
    public DateTimeOffset DateTime { get; set; }
    public int? DetectorModeId { get; set; }
    public int? StationModeId { get; set; }
    public decimal Status { get; set; }
}

var orderedObjects1 = ObjectContext.DetectorStatus.OrderBy(s => s.Status).Select(s => new StatusTransferObject
{
    DetectorId = s.DetectorID,
    DateTime = s.DateTime,
    DetectorModeId = s.DetectorModeID,
    StationModeId = null,
    Status = s.Status
});
var orderedObjects2 = ObjectContext.StationStatus.OrderBy(s => s.Status).Select(s => new StatusTransferObject
{
    DetectorId = s.DetectorID,
    DateTime = s.DateTime,
    DetectorModeId = null,
    StationModeId = s.StationModeID,
    Status = s.Status
});
var mergedObjects = MergeSorted(orderedObjects1, orderedObjects2, (x1, x2) => x1.Status.CompareTo(x2.Status)).ToList();

MergeSorted is taken from the following question: Is there an easy way to merge two ordered sequences using LINQ?

C# Linq Select data from multiple tables

1 Answers1