How to join table by string name using LINQ

Question

I am updating some code which contains text-based queries into LINQ-based queries.

In this example,

var strDeviceType = "Blade";
// code edited for brevity
var strQuery = String.Format(
    " SELECT Test.ID, {0}.Name AS Device " +
    " FROM Test " +
    " INNER JOIN {0} ON DeviceID = {0}.ID ", strDeviceType);

I have run into a problem, which is that the table being joined is specified by a string in C#. The possible values of strDeviceType are "Blade", "Engine", "Diode" (and more), and these tables are all included in my LINQ-to-SQL model.

This is a query joining the Blade table specifically

using (MyDataContext dc = new MyDataContext())
{
    var result = from test in dc.Tests
                 join blade in dc.Blades on test.DeviceID equals blade.ID
                 select new { test.ID, blade.Name };
}

How would I replace ... in dc.Blades ... with the proper collection? i.e. how do I get from the string "Blade" to the collection called Blades in the model?

Here is the definition of Blade and Blades in the auto-generated model (vb.net)

<Global.System.Data.Linq.Mapping.TableAttribute(Name:="dbo.Blade")>  _
Partial Public Class Blade
    Implements System.ComponentModel.INotifyPropertyChanging, 
    System.ComponentModel.INotifyPropertyChanged
' ...

Public ReadOnly Property Blades() As System.Data.Linq.Table(Of Blade)
    Get
        Return Me.GetTable(Of Blade)
    End Get
End Property

The other tables are defined similarly. They also have the fields ID, Name - but there is no base class or Interface which they all share which guarantees these properties.

Edit

I should have included originally that my intent is to make this query accommodate future additions of tables with ID, Name, which would be used in a similar way, without recompiling the project it is in. The project with the linq-to-sql model can be recompiled easily when tables are added.

Rahul · Accepted Answer · 2016-07-25 20:52:26Z

1

NO, you can't do it like the way you are doing with string query cause in that case you loose static type safety. You can include a if..else condition and based on that evaluation change the collection to join like

if(strDeviceType == "Blade")
{
  using (MyDataContext dc = new MyDataContext())
{
    var result = from test in dc.Tests
                 join blade in dc.Blades on test.DeviceID equals blade.ID
                 select new { test.ID, blade.Name };
}
}
else
{
  using (MyDataContext dc = new MyDataContext())
{
    var result = from test in dc.Tests
                 join engine in dc.Engine on test.DeviceID equals engine.ID
                 select new { test.ID, engine.Name };
}
}

answered Jul 25, 2016 at 20:52

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

djv Over a year ago

I've ruled this approach out, because the source of the string could in the future include an additional table name. In which case I would need to recompile to add another case like your example. I am trying to avoid this.

Sam I am says Reinstate Monica · Accepted Answer · 2016-07-25 21:05:56Z

0

public class Device
{
    [Key]
    public int ID { get; set; }

    public int Name { get; set; }

    // if it must be a string, you can use this.
    // public string Type{ get; set; }

    //I personally prefer this approach
    public DeviceType Type { get; set; }
}

public enum DeviceType
{
    Blade = 1, 
    Engine = 2,
    Diode = 3,
}

edited Jul 25, 2016 at 21:05

answered Jul 25, 2016 at 21:00

Sam I am says Reinstate Monica

31.3k12 gold badges75 silver badges101 bronze badges

7 Comments

djv Over a year ago

How would I use this?

Sam I am says Reinstate Monica Over a year ago

@Verdolino delete the Blade, Engine, and Diode tables and use this instead

djv Over a year ago

I see... but those classes are auto-generated linq-to-sql classes so they are very much tied into the data context. I'd rather not mess with them. i.e. Blade.ID has this attribute:

<Global.System.Data.Linq.Mapping.ColumnAttribute(Storage:="_ID", AutoSync:=AutoSync.OnInsert, DbType:="BigInt NOT NULL IDENTITY", IsPrimaryKey:=true, IsDbGenerated:=true)>

, among all the other SQL specific code

Sam I am says Reinstate Monica Over a year ago

@Verdolino Is your project already in production? or are you still developing it?

djv Over a year ago

It's in production, I'm in continuous improvement

|

Oscar Ortiz · Accepted Answer · 2016-07-25 21:12:09Z

Assuming Test.ID is unique across Blade, Engine and Diode, what you could do inside the query is check of existence on each table and then concatenate if there was a match:

using (MyDataContext dc = new MyDataContext())
{
    var result = 
        (from test in dc.Tests
        let blade = (from t in dc.Blade where t.id == test.DeviceID)
        let engine = (from t in dc.Engine where t.id == test.DeviceID)
        let diode = (from t in dc.Diode where t.id == test.DeviceID)
        select new {
            test.ID,
            Device = 
                    (blade.Count() > 0 ? blade.FirstOrDefault().Name : "")  + 
                    (engine.Count() > 0 ? engine.FirstOrDefault().Name : "") + 
                    (diode.Count() > 0 ? diode.FirstOrDefault().Name : "")
        }
        );


}

Community · Accepted Answer · 2017-05-23 11:58:53Z

I found a way to extend the linq-to-sql classes to implement a common interface, and use that interface in the linq query in the answer here. This works because the linq-to-sql classes are marked partial. When adding more tables, only the data context project will need to be recompiled, which is required anyway by the designer. I used reflection to find the Table<TEntity> property in the linq-to-sql model by plural name.

I added a vb file alongside the linq-to-sql model with the following code:

Public Interface IDeviceTable
    Property ID() As Long
    Property Name() As String
End Interface

Partial Public Class Blade
    Implements IDeviceTable
    Public Property ID1 As Long Implements IDeviceTable.ID
        Get
            Return Me.ID
        End Get
        Set(value As Long)
            Me.ID = value
        End Set
    End Property
    Public Property Name1 As String Implements IDeviceTable.Name
        Get
            Return Me.Name
        End Get
        Set(value As String)
            Me.Name = value
        End Set
    End Property
End Class

Partial Public Class Diode
    Implements IDeviceTable
    ' etc.
End Class

and changed the query:

using (TeradiodeDataContext dc = new TeradiodeDataContext())
{
    var strDeviceType = "Blade";
    var pluralDeviceType = System.Data.Entity.Design.PluralizationServices.
                           PluralizationService.CreateService(Application.CurrentCulture).
                           Pluralize(strDeviceType);
    var devices = (System.Data.Linq.Table<IDeviceTable>)dc.GetType().
                   GetProperty(pluralDeviceType).GetValue(dc);

    var result = from test in dc.Tests
                 join blade in devices on test.DeviceID equals blade.ID
                 select new { test.ID, blade.Name };
}

Collectives™ on Stack Overflow

How to join table by string name using LINQ

4 Answers 4

1 Comment

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related