CODEWITHSUNDEEP
c#sqllinq
Ben
Ben

Reputation: 187

How to order data with matching ID from another table using linq?

So I have 2 tables and I am trying to retrieve the data from table 1, however I need it sorting. Table 1 contains an ID from table 2 which refers to a string, I need to order the table 1 data by this string in table 2.

For example...

TABLE 1

   COL_A    |    COL_B   |   COL_C
     1      |      3     |   sample
     2      |      1     |    test
     3      |      2     |   string

TABLE 2

   COL_A    |    COL_B
     1      |    my name
     2      |    his name
     3      |    her name

So I need to order all the data in TABLE 1 by COL_B, but order it by the string in TABLE 2 where the ID matches TABLE 1 COL_B with TABLE 2 COL_A.

How can I do this using LINQ?

Upvotes: 0

Views: 773

Answers (4)

Musa Hafalir
Musa Hafalir

Reputation: 1770

You basically need to join two tables, then order by TABLE2.COL_B, then select TABLE1. Below linq expression should work.

from t1 in TABLE1
join t2 in TABLE2 on t1.COL_B equals t2.COL_A
orderby t2.COL_B
select t1

Upvotes: 2

aevitas
aevitas

Reputation: 3833

This is probably the shortest way.

var ret = TABLE1.Join(TABLE2, a => a.COL_A, b => b.COL_A, (a, b) => new {COL_C = b.COL_C)}).OrderBy(s => s.COL_C);

Upvotes: 0

Oyyou
Oyyou

Reputation: 618

So I wrote this, and used Musa to help finish it off.. I think it's what you want

public class table1
{
   public int a;
   public int b;
   public string c;
}

public class table2
{
   public int a;
   public string b;
}

void Main()
{
List<table1> table1 = new List<table1>()
{
    new table1(){ a=1,b=2, c="RAWR"},
    new table1(){ a=2,b=4, c="DERP"},
    new table1(){ a=3,b=1, c="FOO"},
    new table1(){ a=4,b=3, c="BAR"},
};

List<table2> table2 = new List<table2>()
{
    new table2(){a=1,b="A"},
    new table2(){a=2,b="B"},
    new table2(){a=3,b="D"},
    new table2(){a=4,b="C"},
};

var something = from t1 in table1
join t2 in table2 on t1.b equals t2.a
orderby t2.b
select t1;

Console.WriteLine (something);

}

Upvotes: 1

Hunter
Hunter

Reputation: 2470

I have given the solution below

class Table1
{
    public int IdA { get; set; }
    public int IdB { get; set; }
    public string Table1StrValue { get; set; }
}

class Table2
{
    public int IdA { get; set; }
    public string Table2StrValue { get; set; }
}

var table1List = new List<Table1>()
    {
        new Table1 {IdA = 1, IdB = 3, Table1StrValue = "sample"},
        new Table1 {IdA = 2, IdB = 1, Table1StrValue = "test"},
        new Table1 {IdA = 3, IdB = 2, Table1StrValue = "string"},
    };
var table2List = new List<Table2>()
    {
        new Table2 {IdA = 1, Table2StrValue = "my Name"},
        new Table2 {IdA = 2, Table2StrValue = "his Name"},
        new Table2 {IdA = 3, Table2StrValue = "her Name"},
    };

var result = from table2 in table2List
        join table1 in table1List on table2.IdA equals table1.IdA
        orderby table2.Table2StrValue
        select new {table2.IdA, table2.Table2StrValue, table1.Table1StrValue};

Upvotes: 1

Related Questions