Need Help Translating SQL Server UNION Syntax to LINQ

Question

Hi.

I have the below SQL which works just fine:

SELECT     Message, CreateDate, AccountId, AlertTypeId
FROM       dbo.Alerts
UNION
SELECT     TOP (100) PERCENT Status, CreateDate, AccountId,
                          (SELECT     10 AS Expr1) AS AlertTypeId
FROM         dbo.StatusUpdates
WHERE AccountId = PassedInParameter
ORDER BY CreateDate DESC

I am trying to convert it to LINQ, which doesn't work just fine :) Obviously, there is a lot wrong here - it is just a rough start. It does not account for the above temp column or the order by condition and the generics / return type ambiguity is my attempt to make sense of the two different return types:

public List<T> GetSomething<T>(Int32 accountId)
{
   List<T> result;

   using (DataContext dc = _conn.GetContext())
   {
      IEnumerable<Alert> alerts = (from a in dc.Alerts
                                   where a.AccountId == accountId
                                   select a);
      IEnumerable<StatusUpdate> updates = (from s in dc.StatusUpdates
                                           where s.AccountId == accountId 
                                           select s);

      IEnumerable<T> obj = alerts.Union(updates);

      result = obj.ToList();
   }

   return result;
}

The problems I am having are:

1) I am dealing with two different types (Alerts and StatusUpdate) in my selects and I am not sure how to combine them (or what type to return). I am guessing this might be solved with generics?

2) In my SQL, I have this code: (SELECT 10 AS Expr1) AS AlertTypeId which adds the value ten to the temp column AlertTypeId (allowing the union to match it to Alert's real column AlertTypeId). How are temp columns such as this accomplished in LINQ / how do I do this?

Thanks for your help.

EDIT---------------------------------EDIT------------------------------------------EDIT

OK, I am a little further along. Below is what I have currently. You will notice I added some logic to return the updates for friend relations. I also made this a generic method of type IList given that alerts and updates have to be generic to agree. I pass in StatusUpdate in the calling method (further down below).

public IList GetUpdatesByAccountId<T>(Int32 accountId)
{
    List<Friend> friends = _friendRepository.GetFriendsByAccountId(accountId);

    using (DataContext dc = _conn.GetContext())
    {
        // Get all the account ids related to this user
        var friendAccountIds =
            friends.Select(friend => friend.MyFriendsAccountId).Distinct();
            friendAccountIds = friendAccountIds.Concat(new[] { accountId });

        var updates =
            dc.StatusUpdates.Where(s => s.AccountId.HasValue && friendAccountIds.Contains(s.AccountId.Value)).Select(
                s => new { Alert = (Alert)null, StatusUpdate = s});

        var alerts =
            dc.Alerts.Where(a => a.AccountId == accountId).Select(
                a => new {Alert = a, StatusUpdate = (StatusUpdate) null});

        var obj = updates.Union(alerts).Take(100);

        return obj.OrderByDescending(su => su.StatusUpdate.CreateDate).ToList();

    }

}

And, the calling method:

protected void LoadStatus()
{

    repStatusUpdates.DataSource = _statusRepository
        .GetUpdatesByAccountId<StatusUpdate>(_userSession.CurrentUser.AccountId);

    repStatusUpdates.DataBind();

}

AND here are the interfaces to the repositories I am using to access my Alert and StatusUpdate tables via LINQ:

public interface IAlertRepository
    {
        List<Alert> GetAlertsByAccountId(Int32 accountId);
        void SaveAlert(Alert alert);
        void DeleteAlert(Alert alert);
    }

public interface IStatusUpdateRepository
    {
        StatusUpdate GetStatusUpdateById(Int32 statusUpdateId);
        List<StatusUpdate> GetStatusUpdatesByAccountId(Int32 accountId);
        List<StatusUpdate> GetFriendStatusUpdatesByAccountId(Int32 accountId, Boolean addPassedInAccount);
        void SaveStatusUpdate(StatusUpdate statusUpdate);
        List<StatusUpdate> GetTopNStatusUpdatesByAccountId(Int32 accountId, Int32 number);
        List<StatusUpdate> GetTopNFriendStatusUpdatesByAccountId(Int32 accountId, Int32 number, Boolean addPassedInAccount);        
    }

Current Problems:

1) When I compile this code, I get this strange error:

Unable to cast object of type 'System.Data.Linq.SqlClient.SqlNew' to type 'System.Data.Linq.SqlClient.SqlValue'.

The only reading I can find on it is this link although there isn't a clear solution there (at least that I can tell). However, if the above LINQ code does not look good to you, maybe whatever you suggest will cause this error to disappear.

2) The above code is still not accounting for this line from the original SQL:

(SELECT 10 AS Expr1) AS AlertTypeId

but this is minor.

Thanks again for the help.

Answer 1

Try this (i converted the StatusUpdate to an alert, if this isn't acceptable, you're going to have to either convert the Alert to a StatusUpdate, or create a new class):

var alerts = (from a in dc.Alerts
              where a.AccountId == accountId
              select a);
var updates = (from s in dc.StatusUpdates
               where s.AccountId == accountId 
               select s)
              .OrderByDescending( x => x.CreateDate)
              .Take(100)
              .Select( x => new Alert 
                {
                   Message = x.Percent.ToString(),
                   CreateDate = x.CreateDate, 
                   AccountId = x.AccountId, 
                   AlertTypeId = 10 // Is this right?
                 }
               );

 var obj = alerts.Union(updates);

 result = obj.ToList();

The reason I do the Select last is so that you don't have to construct a new alert for all the results your are not using.

This will give you a list of Alerts.

Using a generic in this situation is sort of hard to pull off. For instance, you can't do this:

IQueryable alerts = (from a in _alerts where a.AccountId == accountId select a);

Because that implicitly converts a to type T. Even if you try to limit what T implements or inherits from:

public List<T> GetSomething<T>(Int32 accountId) where T : IAlert// Interface that both StatusUpdates and IAlert implement
public List<T> GetSomething<T>(Int32 accountId) where T : Alert
public List<T> GetSomething<T>(Int32 accountId) where T : AlertBase // Base class for both Status and Alert

You'll still run into problems because there is no way to statically know exactly what type T is, so you cannot know if it can be converted from Alert and StatusUpdate.

An alternative is to explicitly use IAlert as your return type:

public List<IAlert> GetSomething(Int32 accountId)

With IAlert:

public interface IAlert
{
    int AccountId { get; set; }
    int AlertTypeId { get; set; }
    DateTime CreateDate { get; set; }
    string Message { get; set; }
}

If you have have both Alert and StatusUpdate implement IAlert, you could rewrite it as so:

IQueryable<IAlert> alerts = (from a in dc.Alerts
              where a.AccountId == accountId
              select a);
IQueryable<IAlert> updates = (from s in dc.StatusUpdates
               where s.AccountId == accountId 
               select s)
              .OrderByDescending( x => x.CreateDate)
              .Take(100);

 var obj = alerts.Union(updates);

 result = obj.ToList();

This is the route I would take instead of passing in some unknown type and trying to limit what it implements or inherits, because casting to that type might still be invalid.

Answer 2

You can only take unions of sequences of equal types. You need to convert alerts and updates to sequences of a common type, then take the union. You can do so using anonymous types. Especially useful if the types don't have anything in common.

//this is a hack and probably not what you would want to use.
var alerts =
        from a in dc.Alerts
        where a.AccountId == accountId
        select new { Alert = a, StatusUpdate = (StatusUpdate)null };
var updates =
        from s in dc.StatusUpdates
        where s.AccountId == accountId 
        select new { Alert = (Alert)null, StatusUpdate = s };

//both are sequences of anonymous type with properties:
//    Alert (of type Alert)
//    StatusUpdate (of type StatusUpdate)
var obj = alerts.Union(updates);

---

If you have fields in common, you'd still use anonymous types except you'd include the known fields.

var alerts =
        from a in dc.Alerts
        where a.AccountId == accountId
        select new
        {
            a.Message, //assuming is a string
            Status = (string)null,
            a.CreateDate,
            a.AccountId,
            a.AlertTypeId //assuming is an int
        };
var updates =
        (from s in dc.StatusUpdates
        where s.AccountId == accountId
        select new
        {
            Message = (string)null,
            s.Status, //assuming is a string
            s.CreateDate,
            s.AccountId,
            AlertTypeId = 10 //this should handle the "10 AS AlertTypeId" part
        }).OrderByDescending(s => s.CreateDate);

var obj = alerts.Union(updates);

The key is that both anonymous types has the same exact properties of the same exact types. Then you can take the union between them both.