Retrieving value from Table

Question

I have a table containing bank acct type for users in the database as following

BankAcctTypes

|  ID   |   UserID   |  AcctType   |   AcctNo  |
------------------------------------------------
|  1    |   User1    |  Single     |  1234     |
|  2    |   User2    |  Single     |  2345     |
|  3    |   User2    |  Joint      |  3456     |
|  4    |   User2    |  Joint      |  4567     |
|  5    |   User3    |  Joint      |  5678     |
|  6    |   User3    |  Joint      |  6789     |
|  7    |   User4    |  Single     |  7890     |
|  9    |   User4    |  Single     |  8901     |
|  10   |   User5    |  Joint      |  9012     |
------------------------------------------------

** Users can have any number of joint and single accts and in any combination whatsoever. Any user that has no user acct doesn't figure in the given table.

Now the requirement is to fetch all users from this table with their bank acct types as per following rule

1. If only single type bank acct( one or many) return Single
2. If only Joint type bank acct (one or many) return Joint
3. If a mix of both Joint or Single(in any combination) return Joint

ps: any one the multiple accts to be returned. no restriction on that

Result expected back:

|  ID   |    UserID    | AcctType  | AcctNo | 
---------------------------------------------
|  1    |   User1      |  Single   | xxxxxx |  
|  2    |   User2      |  Joint    | xxxxxx | (any one of Joint acct)
|  3    |   User3      |  Joint    | xxxxxx | 
|  4    |   User4      |  Single   | xxxxxx | (any one of Joint acct)
|  5    |   User5      |  Joint    | xxxxxx | 
---------------------------------------------

Please help me frame the LINQ statement/ SQL statement for the same.

Answer 1

I created the following model based on your sample data:

public enum AcctType
{
    Single,
    Joint
}

public class Account
{
    public int ID;
    public string UserID;
    public AcctType AcctType;
    public int AcctNo;
}

This query should work for you:

var result = from account in accounts
             group account by account.UserID
             into grouping
             select grouping.Any(account => account.AcctType == AcctType.Joint) && grouping.Any(account => account.AcctType == AcctType.Single)
             ? grouping.Where(account => account.AcctType == AcctType.Joint).Take(1)
             : grouping.ToList();

What this does is the following:

1. Go through all accounts
2. Group accounts by UserID
3. Check each grouping to see if it contains at least one joint account and one single account:
   • If so, filter the grouping to get all joint accounts, then take the first one
   • If not, get all accounts

EDIT:

Sorry, I didn't notice you were using EF. The reason you are getting this error is because LINQ to SQL does not support tree-like results. This means that doing this all in a single query is out of the question.

You'll have to split it up like so:

var groupings = accounts.GroupBy(account => account.UserID);
var joint = groupings.Where(grouping => grouping.Any(account => account.AcctType == AcctType.Joint) && grouping.Any(account => account.AcctType == AcctType.Single));
var result = joint.Select(grouping => grouping.First(account => account.AcctType == AcctType.Joint)).Concat(groupings.Except(joint).SelectMany(grouping => grouping.ToList()));

The split into 3 operations isn't as elegant but its necessary in order to avoid repeating certain operations such as the group-by.

Answer 2

You wrote:

the requirement is to fetch all users with the bank account type they are having provided

So the results are mainly properties of users, with some of the properties of THE bank account they have provided, with some bla-bla about single and joint bank accounts.

Later you write:

There could be users with more than one single accts and no joint accts as well as vice versa.

Your requirements are not complete.

Properly specified:

• User with exactly one single account and no joint account: return (selection of) user properties with (selection of) properties of the one and only single account
• User with zero or more single accounts and exactly one joint account: return (selection of) user properties with (selection of) properties of the one and only joint account

not specified:

• User with zero single accounts and zero joint accounts
• user with more than one single account and zero joint accounts
• user with more than one joint accounts

You could decide to return the UserId, AcctType and a sequence of AcctNo of all bank account types with AcctType that the user has.

You could also decide to repeat the UserId and AcctType for every bank account type.

Of course the first is more efficient, as the UserId and AcctType are fetched only once, even if the user has hundreds of bank accounts.

Assuming you have a proper Entity-Framework class definition with a standard one-to-many relation between User and BankAccountType:

class User
{
    public int Id {get; set;}

    // a user has zero or more bank accountTypes
    public virtual ICollection<BankAccountType> BankAccountTypes {get; set;}

    ...
}

enum AcctType
{
    single,
    joint,
};

class BankAccountType
{
    public int Id {get; set;}

    // every bank account type belongs to one user via foreign key UserId:
    public int UserId {get; set;}
    public virtual User User {get; set;}

    public AcctType AccType {get; set;}
    public int AcctNo {get; set;}
}

Your query in small steps:

var result = dbContext.BankAcctTypes
    // Group all BankAcctTypes by the same UserId:
    .GroupBy(bankAcctType => bankAcctType.UserId)

    // From every group, get the Key (which is the UserId)
    // get all elements of the group
    // get a sequence of all joint accounts
    .Select(group => new
    {
        UserId = group.Key,      // the UserId of all bankAcctType in this group
        AllBankAccounts = group,
        JointBankAccounts = group
            .Where(groupElement => groupElement.AccType == AcctType.Joint),

    })

    // if there are joint accounts, keep the joint accounts,
    // otherwise keep AllAccounts (which are all Single, as there are no joint)
    .Select(item => new
    {
        UserId = item.UserId,
        BankAccounts = item.JointBankAccounts.Any() ?
            item.JointBankAccount :
            item.AllBankAccounts,
    })

    // finally extract the values you want:
    .Select(item => new
    {
        UserId = item.UserId,
        BankAccounts = item.BankAccounts.Select(bankAccount => new
        {
            Id = bankAccount.Id,
            AcctType = bankAccount.AcctType,
            AcctNo = bankAccount.AcctNo
        }),
    });