Blank Entry for DataContext

We have a data bound control that is populated with a list of banks.

I can figure out how to Read, Insert and Delete real accounts.

On the form, however, our client needs a way to select a "None" option (some customers use cash).

I cannot insert a "None" entry into the banks data table because of how the data tables are linked to other data tables.

I attempted to insert a "None" record before returning the DataContext so that this option would always appear at the top of the list, but there doesn't appear to be any way to get the data into the collection.

public System.Data.Linq.Table<bank> Banks
{
    get
    {
        return this.GetTable<bank>();
    }
}

I was able to insert a fake record into an IQueryable list:

internal IQueryable<bank> GetBankInfo(bool insertBlank = false)
{
    var list = (from item in banks
                orderby item.bank_name
                select item).ToList();
    if (insertBlank)
    {
        var item = new bank()
        {
            bank_name = String.Empty,
            contact_name = String.Empty,
            contact_phone_num = String.Empty,
            routing_num = "None",
            source = String.Empty,
            fact_routing_num = String.Empty,
            note_id = (int?)null,
        };
        list.Insert(0, item);
    }
    return (IQueryable<bank>)list;
}

But, I can't use this because it throws a TargetInvocationException:

Unable to cast object of type 'System.Collections.Generic.List1[Billing.DataDB.bank]' to type 'System.Linq.IQueryable1[Billing.DataDB.bank]'.

How do I insert a blank record for the DataContext display?

I do not want the blank record in the database.

Solution

You can fix this two ways:

First, change the return type of the method to IEnumerable<T>...

internal IEnumerable<bank> GetBankInfo(bool insertBlank = false)

...and leave out the cast in the return statement. It'll be redundant, as List<T> implements IEnumerable<T>

return list;

Based on what you mentioned in your question, this should work.

Second way:

If you're doing something else with that list/queryable/whatever, such that you really need it to really be IQueryable<T>, that's even easier. Leave the function return type as it was:

internal IQueryable<bank> GetBankInfo(bool insertBlank = false)

And return this:

return list.AsQueryable();

Here's the problem:

I was able to insert a fake record into an IQueryable list:

Actually, you weren't. You enumerated the results of an IQueryable into a new List<T> instance: That's what happened in your ToList() call. It's not a cast, it creates a new collection object. Then you inserted a fake record into the List<T>.

The thing you were casting to IQueryable<bank> wasn't the IQueryable<bank> that you started with; it was a completely different object which contained all the items that were enumerated by the IQueryable<bank>.

The purpose of IQueryable is so the actual query can be executed remotely, as with Entity Framework for example. However, once you materialize the query in the ToList() call, the remote ship has sailed the barn out of the station. Since IQueryable<T> inherits from IEnumerable<T>, I'd tend to use the latter for a general "reference to sequence of things" type. Any method that takes IEnumerable<T> for a parameter will happily consume an IQueryable<T> instead.