How to return values from two different tables?

Question

I’m working on a script for auto complete that you should search two tables of the same level (Courses and Packages) (without relation) and return the merged values in a single list. The problem is that the query below is not returning any value:

[HttpGet]
public JsonResult Autocomplete(Entities db, string search)
{
    JavaScriptSerializer json = new JavaScriptSerializer();

    var query = from c in db.courses.Where(c => c.title.Contains(search))
                from p in db.packages.Where(p => p.title.Contains(search))
                select new { c, p };

    return Json(query.ToList(), JsonRequestBehavior.AllowGet);
}

But when the search is done individually the data is returned normally:

var query = from c in db.courses.Where(c => c.title.Contains(search))
            select new { c };

I need the values related to the search to be returned from both tables at the same time, or individual searches to be done but merged later.

How can I solve?

Answer 1

To return only in a list you can use the method Concat.

Scenario 1:

I have two entities: courses and packages, whereas:

• courses has three fields (id, title and status) and;
• packages has two fields (id and title).

You need to normalize then by placing the same amount of fields in the entity packages and also observe the field type so that there are no conversion errors. The field type has been set bool? because the countryside status of courses is of that type; How to catch the field type: é só olhar na entidade mapeado o seu tipo

using (MyDbContext db = new MyDbContext())
{

    var courses = db.Cours.Select(c => new
    {
        c.Id, 
        c.Title, 
        c.Status
    });

    var packages = db.Packages.Select(c => new 
    {
        c.Id, 
        c.Title, 
        Status = new bool?()
    });

    var js = courses.Concat(packages);
    var items = js.ToList();

}

Scenario 2:

I want to take the same amount of entities' fields and they are the same, for example id and name, how it would be:

using (MyDbContext db = new MyDbContext())
{

    var courses = db.Cours.Select(c => new
    {
        c.Id, 
        c.Title
    });

    var packages = db.Packages.Select(c => new 
    {
        c.Id, 
        c.Title
    });

    var js = courses.Concat(packages);
    var items = js.ToList();

}

---

Observing: the two scenarios who solves is the SQL part of the database, with no low performance.

In your code:

[HttpGet]
public JsonResult Autocomplete(Entities db, string search)
{
    JavaScriptSerializer json = new JavaScriptSerializer();

    var courses = db.Courses.Select(c => new
    {
        c.Id,
        c.Title
    });

    var packages = db.Packages.Select(c => new
    {
        c.Id,
        c.Title
    });

    var js = courses.Concat(packages);

    return Json(js.Where(c => c.Title
                  .Contains(search))
                  .ToList(), JsonRequestBehavior.AllowGet);
}

---

Example Microsoft website:

Concatenate Two Sequences

IQueryable<String> custQuery =
    (from cust in db.Customers
    select cust.Phone)
    .Concat
    (from cust in db.Customers
    select cust.Fax)
    .Concat
    (from emp in db.Employees
    select emp.HomePhone);

foreach (var custData in custQuery)
{
    Console.WriteLine(custData);
}

This example illustrates very well that the field types must be of the same type independent of the field name (although the alias can be made, for a good code reading) and in the same order.

Answer 2

There are some ways to do this, one of them is to return the results through a dynamic list.

It would look something like this:

[HttpGet]
public JsonResult Autocomplete(Entities db, string search)
{
    JavaScriptSerializer json = new JavaScriptSerializer();

    return Json(new { 
                  couses = db.courses.Where(c => c.title.Contains(search)).ToList(),
                  packages = db.packages.Where(p => p.title.Contains(search)).ToList()
        }, JsonRequestBehavior.AllowGet);
}

So you’ll get a return like this:

{
  "couses": [
    {
      //Lista de courses
    }
  ],
  "packages": [
    {
      //Lista de Packages
    }
  ],
}

Or you can create a Viewmodel, leaving everything typed and organized.

But if one table has no relation to the other, I wouldn’t try to encompass everything in the same select, otherwise it becomes a mess and difficult to maintain.

Answer 3

You can use the SelectMany for merged their values, only the one question that would be for a case where db.packages.Where(p => p.title.Contains(search), is compared with a variable of the db.courses.Where(c => c.title, but as you did not pass the model become difficult to create a better solution, but the way this I believe it solves.

    var query = db.courses.Where(c => c.title.Contains(search)
      .SelectMany(C => db.packages.Where(p => p.title.Contains(search).DefaultIfEmpty()
      , (n, a) => new
            { n, a }
      );

See the reference.