How would I write this as a single LINQ query?

Question

Using the following logic, what would the correct syntax be for a single LINQ query?

If Branch is Service, I want its parent, otherwise I want Branch.

Can you critique my attempt and let me know how I can improve it?

int branchId = 21;
var t = ctx.BranchInfos.Single(p => p.BranchID == branchId );
if (t.Type == BranchType.Service.ToString())
{
    t = ctx.BranchInfos.Single(p => p.BranchID == t.Parent);
}

Answer 1

I am quite sure you can do it with a single LINQ statement, but I am equaly sure that you should not do this. It will not improve the readabilty and hardly the performance.

var t = ctx.BranchInfos.Single(x =>
  (
    x.BranchID == branchID &&
    x.Type != BranchType.Service.ToSting()
  )
  ||
  (
    ctx.BranchInfos.Any(
      y.BranchID == branchID) &&
      y.Type == BranchType.Service.ToSting()) &&
    x.BranchID == ctx.BranchInfos.Single(
      y.BranchID == branchID) &&
      y.Type == BranchType.Service.ToSting()).ParentID
  )
);

Nice, isn't it? :D I still suggest not to use it. The first case is simple - if the item has the correct ID and and is not of type Service we have a match.

The second case is more tricky. We have to check if the item has the ID from the ParentID property of the item with the supplied ID but only if the item with the supplied ID is of type Service. Because we do not know if there is an item with the supplied ID and type Service when we check this, we must first check with Any() if there is such an item and rely on the conditional evaluation of the and.

Answer 2

var t = ctx.BranchInfos.Single(
  p => (p.BranchID == branchId && p.Type != BranchType.Service.ToString) ||
       (p.BranchID == GetBranchParentId(branchId) && p.Type == BranchType.Service.ToString));

where GetBranchParentId is a function wich returns the BranchId of the Branch who's id is passes as a parameter.

But, I like your original code, so I wouldn't use a single query to get my data.

Answer 3

I think you could do something like this:

var t = ctx.BranchInfos.FirstOrDefault(p => p.BranchID == branchId || p.BranchID == t.Parent);

Mike

Answer 4

This will probably get you what you need unless I messed up the logic.

Edit: Different approach

var t = ctx.BranchInfos.Where(p.BranchID == branchId).First(p => p.Type == BranchType.Service.ToString() ? p.Parent : p);

Answer 5

This'll probably work but it's a bit awkward.

var t = ctx.BranchInfos.Where(p => p.BranchID == branchId)
    .Select(p => 
      p.Type != BranchType.Service.ToString() 
      ? p 
      : ctx.BranchInfos.Single(t => p.Parent == t.BranchId)).FirstOrDefault();

Answer 6

I suggest that if this is only needed in one place then what you have now is reasonably clear and should be kept.

If you are doing this a lot then do something like:

public static BranchInfo BranchOrServiceParent(
    this IEnumerable<BranchInfo> input)
{ 
    var t = BranchInfos.Single(p => p.BranchID == branchId);
    if (t.Type == BranchType.Service.ToString())    
        t = input.BranchInfos.Single(p => p.BranchID == t.Parent);
    return t;
}

Then using it is as simple as:

int branchId = 21;
var t = ctx.BranchInfos.BranchOrServiceParent();

If you subsequently need to parameterize/change thing things you can in a clear fashion.

If you subsequently find that the two possible trips to the database are a performance issue then you can either try a complex Linq query or accept that this probably needs to actually be done by a stored procedure.

Answer 7

I believe the following is equivalent to your code sample. I have added some mock code to turn this into a self-contained example.

using System;
using System.Collections.Generic;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Context ctx = new Context();
            ctx.BranchInfos.Add(new BranchInfo() { Type = "NonService", BranchID = 20, Parent = 0 });
            ctx.BranchInfos.Add(new BranchInfo() { Type = "Service", BranchID = 21, Parent = 20 });
            ctx.BranchInfos.Add(new BranchInfo() { Type = "NonService", BranchID = 30, Parent = 20 });

            int branchId = 21;

            var t = (from a in ctx.BranchInfos
                     where a.BranchID == branchId
                     select a.Type != BranchType.Service.ToString() ? a :
                     (from b in ctx.BranchInfos
                      where b.BranchID == a.Parent
                      select b).Single()).Single();

            Console.WriteLine(t.BranchID); // Prints 20
        }

        class Context
        {
            public List<BranchInfo> BranchInfos = new List<BranchInfo>();
        }

        class BranchInfo
        {
            public string Type;
            public int BranchID;
            public int Parent;
        }

        enum BranchType
        {
            Service = 0
        }
    }
}

Answer 8

var t = ctx.BranchInfos.Where(p =>
 (
   p.BranchID == branchID &&
   p.Type != BranchType.Service.ToSting()
 )
 ||
 (
   p.Type == BranchType.Service.ToSting() &&
   ctx.BranchInfos.Where(p => p.BranchID == branchID).FirstOrDefault() != null &&
   p.BranchID == ctx.BranchInfos.Where(p => p.BranchID == branchID).FirstOrDefault().ParentID
 )).FirstOrDefault();

The logic here is: (Get me Branch By ID IF the type is of service) OR (get me the parent of a Branch where I know the the child ID if the branch type is Service)

ALSO:

Even though there's a subquery in there, it will evaluate to a single hit to the DB because you're using the same Datacontext in the subquery.

Answer 9

Provided BranchInfo.Parent is the same type as BranchInfo:

int branchID;
var branchOrParent = db.BranchInfos
    .Where(b => b.BranchID == branchID)
    .Select(b => b.Type == BranchType.Service.ToString() ? b.Parent : b)
    .FirstOrDefault();