C# EF if row exists in table one select it else select from another table

Question

I'm having two tables T1 and T2.

T1
---------
(Name,Value)

T2
---------
(Name,Value)

I'm trying to make a select on T1 based on Name and if it doesn't exist then select the row from T2

var T1Row = db.T1.AsNoTracking().Where(s => s.Name == "text1").FirstOrDefault();
if (T1Row == null)
    T1Row = db.T2.AsNoTracking().Where(s => s.Name == "text2").FirstOrDefault();

But I'm getting Cannot implicitly convert type T2 to T1. I'm trying to make this two queries as a single one but I don't know how.

I'm new to C# programming, so please be gentle

T1Row is strongly typed with T1 so you can't use same variable for getting T2 records. So try to use different variable for second query. — JyothiJ, Mar 12 '18 at 12:36

score 0 · Answer 1 · answered Mar 12 '18 at 12:37

0

T1 and T2 are different types (even if they have the exact same properties), consider using an interface, or selecting/projecting into a common object

answered Mar 12 '18 at 12:37

TheGeneral

79,002
9
103
141

Alexey Klipilin · Accepted Answer · 2018-03-12T12:56:17.710

These two queries result in different types. Looks like you need DTO classes. In that way you can select your data in required format and return to your BLL.

public class NameValueDTO
{
    public string Name { get; set; }
    public string Value{ get; set; }
}

var T1Row = db.T1.AsNoTracking().Where(s => s.Name == "text1").Select(i => new NameValueDTO { Name = i.Name, Value = i.Value }).FirstOrDefault();
if (T1Row == null)
    T1Row = db.T2.AsNoTracking().Where(s => s.Name == "text2").Select(i => new NameValueDTO { Name = i.Name, Value = i.Value }).FirstOrDefault();

score 0 · Answer 3 · answered Mar 12 '18 at 12:39

0

You're seeing this error because db.T1 and db.T2 are different class types. One option is to have the two classes you're using for T1 and T2 implement the same interface. Then, instead of using var, use the interface type when declaring T1Row:

IMyInterface T1Row = db.T1.AsNoTracking().Where(s => s.Name == "text1").FirstOrDefault();  //both the types in T1 and T2 need to implement this interface.
if (T1Row == null)
    T1Row = db.T2.AsNoTracking().Where(s => s.Name == "text2").FirstOrDefault();

answered Mar 12 '18 at 12:39

Noel

3,288
1
23
42

but these are DB tables, not sure we need to implement `interface` for ORM mapped classes – Alexey Klipilin Mar 12 '18 at 12:45
@Alexey - The fact that the collections `db.T1` and `db.T2` represent database tables is unrelated to the compile time error OP is seeing. C# is statically typed, and the type of `T1Row` is bound on the very first line to whatever type `T1` is. Trying to automatically convert from whatever type `T1` is to whatever type `T2` is (which OP is doing on the 3rd line) will result in a compile time error. – Noel Mar 12 '18 at 12:52
You should use a DTO or POCO to encapsulate the data not an interface. – Héctor Espí Hernández Mar 12 '18 at 13:38

score 0 · Answer 4 · answered Mar 12 '18 at 12:40

0

You should use Select function to convert T1 or T2 objects to an object (nor T1, neither T2) that contains the properties (Name and Value) that you need in return.

answered Mar 12 '18 at 12:40

Héctor Espí Hernández

860
10
11

C# EF if row exists in table one select it else select from another table

4 Answers4