Entity Framework query table with different name

Question

I have a few tables which are exactly the same (except the table name). The data are different, and due to some specific reason, they cannot be merged into one single table.

Example:

TableA (
    Value1 INT NULL,
    Value2 INT NULL
)

TableB (
    Value1 INT NULL,
    Value2 INT NULL
)

.... etc ....

I need to perform same complex calculations / operations to all the tables (which are exactly the same). How can I do that with EF?

In traditional SQL query, we can construct query string by altering the FROM <table name>. But I can't figure how I can do that with EF

Note: EF model-first approach

Thanks in advance

Answer 1

This question might be a possible duplicate Here is the link,

You can use above post to get the list of entities you want.

Try this query when you have the list of entities you want

      foreach (var table in entities)
        {
          var record =  (from x in table 
                where x.Value1 == 1
                select new CommonTableModel()
                {
                          Value1  = x.Value1 , 
                          Value2  = x.Value2 
                }).ToList();
        }

Answer 2

1. Inherit both classes(Employer, Customer) from common parent class - Person.
2. All common methods should become generic with constraint - where T : Person, this way you will have access to all properties of both classes inside these methods.

Implementation:

public abstract class Person
{
    public int Id {get;set;}
    public string Name {get;set;}
    public int Age {get;set;}
}

public class Employer : Person
{}

public class Customer : Person
{}

public List<T> GetPersonsOlderThan20Years<T>() where T : Person, new()
{
    //any common logic
    return context.Set<T>().Where(x => x.Age > 20).ToList();
}

Usage:

var employers = GetPersonsOlderThan20Years<Employer>();
var customers = GetPersonsOlderThan20Years<Customer>();

Answer 3

create a generic entity

for specific row in an entity

//int id or whatever your [key] attribute is
public T GetSelected<T>(int id) where T : class
{
    return _dbContext.Set<T>().FirstOrDefault(x => x.Id == id);
}

var selectedFromEntity1 = GetSelected<Entity1>(idOfEntity1);
var selectedFromEntity2 = GetSelected<Entity2>(idOfEntity2);

for all the rows of an entity

public List<T> GetAll<T>() where T : class
{
    return _dbContext.Set<T>().ToList();
}

var dataFromEntity1 = GetAll<Entity1>();
var dataFromEntity2 = GetAll<Entity2>();

or if you want to add paging or conditions

public IQueryable<T> GetAll<T>()
{
   return _dbContext.Set<T>().AsQueryable();
}


var dataFromEntity1 = GetAll<Entity1>();
var resultFrom1 = dataFromEntity1
                     .Where(x => x.Property1 == "searchFilter") //user input searchFilter
                     .Skip(pageSize* pageNumber) //user input page size and page number
                     .Take(pageSize) //user input page size
                 .ToList();

var dataFromEntity2 = GetAll<Entity2>();
var resultFrom2 = dataFromEntity2
                     .Where(x => x.Property1 == "searchFilter") //user input searchFilter
                     .Skip((pageSize * pageNumber)-pageSize) //user input page size and page number
                     .Take(pageSize) //user input page size
                 .ToList();

Answer 4

I think raw queries are your anser: Raw SQL

So you can basically:

   using (var context = new BloggingContext()) 
   {
      var blogs = context.Blogs.SqlQuery("SELECT * FROM dbo.Blogs").ToList(); 
   }

And exchange the name of the table as you suggested.

Note: I have not tried this, and for sure the tables must be completely identical (even column ordering)