Can Linq-To-Sql be aware of similarities of two tables?

Question

Imagine I have 2 tables in my database, with the same schema (columns, types, etc). Can Linq-To-Sql treat them as Table<AClass> using the same class for both of them? How to do it?

Answer 1

The UNION and CONCAT operator can be used across different tables as easily as a single table multiple times. A link to the UNION and CONCAT operators: http://blog.benhall.me.uk/2007/08/linq-to-sql-difference-between-concat.html

If you have slight differences, you can hide them by creating a wrapper object that only exposes the fields that are the same (and renames any mismatches).

Here is a LINQPAD example I whipped up using two mismatched tables. It worked fine (merging a fake row I added to the second as expected):

var set1 = from  t in TestTriggers
select new {t.TestTriggerID} ;

var set2 = from t in TestTrigger2s
select new {t.TestTriggerID} ;

set1.Dump();
set2.Dump();

set1.Union(set2).Dump();
set1.Concat(set2).Dump();

Both tables had other columns, but the anonymous type hides that. The results:

IOrderedQueryable<> (1 item)  
TestTriggerID 
1


IOrderedQueryable<> (2 items)  
TestTriggerID 
1

2


IOrderedQueryable<> (2 items)  
TestTriggerID 
1

2


IOrderedQueryable<> (3 items)  
TestTriggerID 
1

1

2

You can see that the CONCAT duplicated the 1 row from both tables, while the UNION discards duplicates.

Answer 2

One option would be to create a view on the server that unioned the results if it's for query only.

For update scenario's your more stuck as it wouldn't be possible for LINQ to SQL to know which table to use...

Answer 3

No. You can't have two classes with the same name on the same DBML file (same namespace).

Besides, how would LINQ-to-SQL know how which table to insert into or update?

A better question would be: if you have two tables with the same schema, and you want to use them together as a single collection, why not merge them tables into a single table?

Answer 4

For Reading: it is no problem. A query can project into arbitrary types and LinqToSql will match property names to column results.

from c in dc.Customer
where c.Address.StreetName.StartsWith("AB")
select new Person() {Name = c.Name};

A (query such as the one above) is resolved when enumerated by the following operations:

• sql is produced
• DataContext.ExecuteQuery<Person>() is called with that sql string and parameters documetation
  • the sql is sent to the database and a datareader is returned.
  • the datareader is passed to DataContext.Translate<Person>() docmentation.

In the case of this query - only Customer.Name column is fetched and no instances of Customer are new'd up. Also note - if you had a datareader, you could call DataContext.Translate<T> yourself.

If you want to add a property that works like a queryable table of a single type, do this in your partial DataContext file:

public IQueryable<CustomType> CustomTypeTable
{
  get
  {
    var query = this.Type1Table.Select(t1 => new CustomType{f1 = t1.f1 ... })
      .Concat( this.Type2Table.Select(t2 => new CustomType{f1 => t2.f1 ... })
    return query
  }
}

---

For writing... ObjectTracking in DataContext is by Type and PrimaryKey. The Type of each instance is needed to (unambiguously) map to a table in the database. So, you can't have 1 Type map to 2 tables in these scenarios.

Answer 5

If you have two tables with identical names in different schemas, you can achieve the needed behaviour by removing the schema name from the Table attribute and recreating DataContext with different connection string.
If you have two tables with different names and identical structure in the same schema, we don't know any way to map these tables to single class.