CODEWITHSUNDEEP
c#linq-to-sqlfunction
aarona
aarona

Reputation: 37344

LINQ to SQL: How to implement a LEAST or similar function?

Given:

A table named TABLE_1 with the following columns:

I have SQL query where TABLE_1 joins on itself twice based off of ColumnA, ColumnB, ColumnC. The query might look something like this:

Select t1.ID, t2.ID, t3.ID, LEAST(t1.ColumnD, t2.ColumnD, t3.ColumnD)
  From TABLE_1 t1
  Left Join TABLE_1 t2 On
       t1.ColumnA = t2.ColumnA
   And t1.ColumnB = t2.ColumnB
   And t1.ColumnC = t2.ColumnC
  Left Join TABLE_1 t3 On
       t2.ColumnA = t3.ColumnA
   And t2.ColumnB = t3.ColumnB
   And t2.ColumnC = t3.ColumnC

Problem:

I need that Query to be rewritten in LINQ. I've tried taking a stab at it:

var query =
    from t1 in myTABLE1List // List<TABLE_1>
    join t2 in myTABLE1List
      on new {t1.ColumnA, t1.ColumnB, t2.ColumnC}
  equals new {t2.ColumnA, t2.ColumnB, t2.ColumnC}
    join t3 in myTABLE1List
      on new {t2.ColumnA, t2.ColumnB, t2.ColumnC}
  equals new {t3.ColumnA, t3.ColumnB, t3.ColumnC}
  select new {
    ID_1 = s1.ID,
    ID_2 = s2.ID,
    ID_3 = s3.ID,
    // Invalid anonymous type member declarator.
    // Anonymous type members must be declared with a member assignment,
    // simple name or member access.
    // how can I implement this?
    least(s1.ColumnD, s2.ColumnD, s3.ColumnD)
  };

....

private object least(params object[] objects)
{
  // code here that sorts the objects and returns the 'smallest' of them.

  return leastObject;
}

How do I write my query in LINQ? What am I doing wrong? I thought it was possible to use functions inside of LINQ expressions, so why am I getting this error?

Upvotes: 0

Views: 314

Answers (2)

Dan Tao
Dan Tao

Reputation: 128417

I could be wrong, but I think you need to assign the result of least to an actual member of your new anonymous type instance, e.g.:

select new {
    ID_1 = s1.ID,
    ID_2 = s2.ID,
    ID_3 = s3.ID,
    Least = least(s1.ColumnD, s2.ColumnD, s3.ColumnD)
};

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33474

How about?

MinOfColumnD = Math.Min(Math.Min(s1.ColumnD, s2.ColumnD), s3.ColumnD))

EDIT: Assuming that ColumnD in each of the table is numeric & 'least` (in this context) is minimum of 3 numbers in the given columns.

EDIT: If the columns are string, determine what max. values it can hold & do a .Parse on it. For e.g.

MinOfColumnD = Math.Min(Math.Min(int.Parse(s1.ColumnD), int.Parse(s2.ColumnD)), int.Parse(s3.ColumnD)))

Upvotes: 1

Related Questions