How do I 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

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 2

You should first convert it to List;

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

or by using Concat

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

EDIT: sure you have to reassign foo.

Answer 3

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");

Answer 4

It might be a very old question, I would like to add more explanation and sample here

if you use IQueryable<YourObject> , you must convert it to IEnumerable<YourObject> first

addition detail about IEnumerable:

Returning IEnumerable<T> vs. IQueryable<T>

by

IQueryable<YourObject> iqueryResult = // ..... your LinQ or whatever
IEnumerable<YourObject> enumResult = iqueryResult.AsEnumerable();

then, to add you can do it by

enumResult = enumResult.Concat(new[] {new YourObject()
                 { 
                    //.... 
                 } 
             });

sample of real code

var iquery_QRDTR = from rrp in P_QCDTR
        select new WebRequestData
        {
             ros_chk_id = rrp.CHECKIN_ID,
             ros_img = rrp.EMPLOYEE_ASSOCIATE.IMAGE_URL
        };

var enum_QRDTR = iquery_QRDTR.AsEnumerable();

enum_QRDTR = enum_QRDTR.Concat(new[] {new WebRequestData()
             {
             ros_chk_id = 16,
             ros_img = "http://link.to.image/profile.jpg" 
             } });

Answer 5

You can use union

IQueryable<int> foo = new SomeQueryable<int>();
IQueryable<int> foo2 = new SomeQueryable<int>();
foo = foo.Union(foo2);

Answer 6

Try this:

var query = from c in db.clClinics select c; 

var results = query.ToList().Concat(new clClinic[] { new clClinic()});  

Answer 7

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 8

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"} })