How to add a new record to an IQueryable variable?

Question

The IQueryable results is queried from my db using LINQ, How do I add a new record to the IQueryable result.

Answer 1

You can firstly convert it to List;

IQueryable<int> foo = new IQueryable<int<();
List<int> list = foo.ToList();
list.Add(5);

or by using Concat

IQueryable<int> foo = new IQueryable<int<();
foo = foo.Concat(new int[]{5});

EDIT: sure you have to reassign foo.

Answer 2

Do you want to add it to the database, or just to the list of results that other things like databinding will use?

If it's the former, you'll need to use the normal add operation for whatever kind of LINQ you're using, but to the table representation rather than the IQueryable. Queries only read data.

If it's the latter, use Enumerable.Concat to concatenate your existing sequence (the IQueryable) with a new one containing your extra entries (an array would do fine here, or a list), and use the result for binding etc. It's probably worth using AsEnumerable() first to make sure that Enumerable is used instead of Queryable. For example:

IQueryable<string> names = [...];
names = names.AsEnumerable().Concat(new[] { "(None)", "(Add new)" });

Answer 3

Sorry, I tried both of your ways, but it wouldn't work. Below is my code, I'm trying to read data for a gender drop down list:

dcDataContext db = new dcDataContext();
var results = from c in db.tblSexes
              select new { c.SexCode, c.SexName };

              results = results.AsEnumerable().Concat(new[] { new { SexCode = -1, SexName = "Please select your Gender" } });

              SelectList sliSex = new SelectList(results, "SexCode", "SexName");

I got this error: Error 4 The type arguments for method 'System.Linq.Enumerable.Concat(System.Collections.Generic.IEnumerable, System.Collections.Generic.IEnumerable)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Answer 4

Explicitly set properties for anonymous types. Try This:

dcDataContext db = new dcDataContext();
var results = from c in db.tblSexes
              select new { c.SexCode, c.SexName };
results = results.AsEnumerable().Concat(new[] { new { SexCode = -1, SexName = "Please select your Gender"} });
SelectList sliSex = new SelectList(results, "SexCode", "SexName");

Edit: You have to point the exact types of you properties. I mean if it is int you have to point that it is. I guess SexCode is a 'long' and by default -1 is an 'int'. If you cast -1 to long(or the type of SexCode) the problem will be solved.

Here is the exact solution if SexCode's type is long.

dcDataContext db = new dcDataContext();
var results = from c in db.tblSexes
              select new { c.SexCode, c.SexName };
results = results.Concat(new[] { new { SexCode = -1L, SexName = "Please select your Gender"} });
SelectList sliSex = new SelectList(results, "SexCode", "SexName");

Answer 5

Because the results is IQueryable you should cast to it

  dcDataContext db = new dcDataContext();
var results = from c in db.tblSexes
              select new { c.SexCode, c.SexName };
results = results.AsEnumerable().Concat(new[] { new { SexCode = -1, SexName = "Please select your Gender"} }).AsQueryable();
SelectList sliSex = new SelectList(results, "SexCode", "SexName");

Or without any casting

results = results.Union(new[] { new { SexCode = -1, SexName = "Please select your Gender"} })

Answer 6

The simple answer is that unless you add the record to the underlying datastore that the Iqueryable is querying, you can't add a new record into an IQueryable. So if you are using LinqToSql then you would have to add a row into the table that the IQueryable was querying in order to "add" a row into the IQueryable.

Remember that an IQueryable is not a result set, it is a query. Until you use something like .ToList() the IQueryable will not evaluate a result set and more importantly, the IQueryable doesn't hang on to that result set. It creates a list and puts the results into in instead. So that means that if you call ToList() on an Iqueryable twice, it will go off and query the database twice. It doesn't care that it might be inefficient.

So, if you look at the examples that others have used above, a simple change of AsEnumerable() to ToList() will most likely fix your problems.

Something like:

dcDataContext db = new dcDataContext();
var query = from c in db.tblSexes
              select new { c.SexCode, c.SexName };

var results = query.ToList().Concat(new[] { new { SexCode = -1, SexName = "Please select your Gender" } });

//or even better now that it's a list
var results = query.ToList().Add(new { SexCode = -1, SexName = "Please select your Gender" });

SelectList sliSex = new SelectList(results, "SexCode", "SexName");