2

I have two Entity Framework Models which are identical in structure, they were generated using Code First from Oracle DB (2 "twin" tables, please don't ask why, I don't have control over DB).

I was looking for an efficient way to do it with Linq, trying to avoid rewriting same queries twice.

Some examples:

[Table("ORACLE.TABLE1")]
public partial class MyModel1
{
    [Key]
    [Column(Order = 0)]
    [StringLength(3)]
    public string field1 { get; set; }

    [Key]
    [Column(Order = 1)]
    [StringLength(3)]
    public string field2 { get; set; }

    [Key]
    [Column(Order = 2)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int field3 { get; set; }

    // -- many other fields

}

[Table("ORACLE.TABLE2")]
public partial class MyModel2
{
    [Key]
    [Column(Order = 0)]
    [StringLength(3)]
    public string field1 { get; set; }

    [Key]
    [Column(Order = 1)]
    [StringLength(3)]
    public string field2 { get; set; }

    [Key]
    [Column(Order = 2)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int field3 { get; set; }

    // -- many other fields, same as above
}

Somewhere, I have:

    public virtual DbSet<MyModel1> MyModel1s { get; set; }       
    public virtual DbSet<MyModel2> MyModel2s { get; set; }

My current trivial and ugly solution for getting data is this:

        var result1 = from t in db.MyModel1s
                      where t.field1 == myparameter1
                         && t.field2 == myparameter2
                         && t.field3 == myparameter3
                      select t;

        var result2 = from t in db.MyModel2s
                      where t.field1 == myparameter1
                         && t.field2 == myparameter2
                         && t.field3 == myparameter3
                      select t;
        var result = result1.ToList();

... And finally ...

        foreach (var res2 in result2)
        {
            var m1 = new MyModel1
            {
                field1 = res2.field1,
                field2 = res2.field2,
                field3 = res3.field3
            };
            result.Add(m1);
        }

Now result contains all that I want. There must be a better way, suggestions?

tonjo
  • 1,256
  • 1
  • 13
  • 26

2 Answers2

2

You can do something like this using Concat extension method, which is equivalent to UNION ALL in SQL:

    var result= db.MyModel1s.Where(m=>m.field1 == myparameter1
                                   && m.field2 == myparameter2
                                   && m.field3 == myparameter3)
                            .Select(t=>new MyModelDTO
                                      {
                                         field1 = t.field1,
                                         field2 = t.field2,
                                         field3 = t.field3
                                      }
                                   )
                            .Concat( db.MyModel2s.Where(t=> t.field1 == myparameter1
                                                         && t.field2 == myparameter2
                                                         && t.field3 == myparameter3)
                                                 .Select(t=>new MyModelDTO
                                                            {
                                                             field1 = t.field1,
                                                             field2 = t.field2,
                                                             field3 = t.field3
                                                            }
                                                         )
                                   ).ToList();

This way you will get the result in one round trip to your DB. If you want to avoid duplicates use Union extension method instead.

Now, if you don't want to project both queries to a common DTO, then you will need to do two round trips to your DB and do the merge in memory:

    var result= db.MyModel1s.Where(m=>m.field1 == myparameter1
                                   && m.field2 == myparameter2
                                   && m.field3 == myparameter3)
                            .AsEnumerable()// Calling the method you will call Concat extension method from IEnumerable<T> type
                            .Concat( db.MyModel2s.Where(t=> t.field1 == myparameter1
                                                         && t.field2 == myparameter2
                                                         && t.field3 == myparameter3)
                                                 .Select(t=>new MyModel1
                                                            {
                                                             field1 = t.field1,
                                                             field2 = t.field2,
                                                             field3 = t.field3
                                                            }
                                                         )
                                   ).ToList();
octavioccl
  • 35,665
  • 6
  • 76
  • 95
  • Actually, it does not work because of this: http://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query And I cannot create a DTO because I cannot concatenate a DTO with a mapped entity – tonjo Sep 02 '16 at 10:20
  • Right, I forgot that.And can you project both query using the same DTO? I think is the best solution is you want to execute this query in the server side. Otherwise you will need execute two round trips to your database and merge both results in memory. You can do that in just one query using `AsEnumerable` extension method, do you need help about this last idea? – octavioccl Sep 02 '16 at 13:28
  • I think I should try to ask DBAs the creation of the view as suggested by Remus, otherwise yes, I would need help about your last idea. – tonjo Sep 02 '16 at 13:45
  • Ok, in that case, try with my second query ;) – octavioccl Sep 02 '16 at 13:46
  • All very interesting. I didn't mention, though, that I have to build my query according to some rules. For example, if a search field is not empty (in case of string) then add a `.Where`, otherwise ignore it. In this case it would be a bit messy. – tonjo Sep 02 '16 at 13:53
  • 1
    Well in that case you can build your queries in several steps and then at the end you can materialize them doing something like this: var result=`query1.AsEnumerable().Concat(query2).ToList()`; – octavioccl Sep 02 '16 at 13:56
1

Create a view in the DB that does a UNION ALL between the two tables, then query the view instead. Do not do the union client side, you'll miss possible server side optimizations and you won't be able to provide certain semantics like correct order across both tables.

Remus Rusanu
  • 273,340
  • 38
  • 408
  • 539