2

I'm using LINQPad to evaluate my linq query. My query goes like this:

from o in MyTableFirst
join p in MyTableSecond on o.TheName equals p.TheName
where p.TheName == "CBA-123" && !p.Removed && 
   (o.ReturnPeriod ==100 || o.ReturnPeriod ==10)
select new {
   HMax1 = o.MaxValue1,
   HMax2 = o.MaxValue2,
   HMax3 = o.MaxValue3
}


This query can return 0 or some number of rows.

In LINQPad, it return me something like this:

HMax1   HMax2   HMax3
21.1        null         22.5
null         24.6        11.5

Now, how am I going to get the Maximum value out for these return rows & columns?
I'm expecting return of 24.6.

Thank You

Improve this question
7
  • what does MyTableFirst look like? and what is the type of o.MaxValue1? Commented Apr 12, 2013 at 9:50
  • 'CREATE TABLE [dbo].[MyTableFirst]( [Id] [int] IDENTITY(1,1) NOT NULL, [TheName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ReturnPeriod] [smallint] NULL, [MaxValue1] [float] NULL, [MaxValue2] [float] NULL, [MaxValue3] [float] NULL ) ON [PRIMARY]` Commented Apr 15, 2013 at 1:56
  • CREATE TABLE [dbo].[MyTableSecond]( [Id] [int] IDENTITY(1,1) NOT NULL, [TheName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Removed] [bit] NOT NULL ) ON [PRIMARY] Commented Apr 15, 2013 at 2:06
  • INSERT INTO MyTableSecond VALUES ( 'CBA-123', 0); Commented Apr 15, 2013 at 2:35
  • INSERT INTO MyTableFirst VALUES ( 'CBA-123', 10, 21.1, NULL, 22.5); INSERT INTO MyTableFirst VALUES ( 'CBA-123', 100, NULL, 24.6, 11.5); Commented Apr 15, 2013 at 2:36

2 Answers 2

Reset to default
2

How about this:

(from o in db.MyTableFirsts
 join p in db.MyTableSeconds on o.TheName equals p.TheName
 where p.TheName == "CBA-123" && !p.Removed &&
 (o.ReturnPeriod == 100 || o.ReturnPeriod == 10)
  select new
  {
    Maximum = Math.Max(
       Math.Max((float)(o.MaxValue1 ?? 0), (float)(o.MaxValue2 ?? 0)),
       (float)(o.MaxValue3 ?? 0)
    )
  }).OrderByDescending(o => o.Maximum).FirstOrDefault();

Or instead of .OrderByDescending(o => o.Maximum).FirstOrDefault(), you can use .Max(o => o)

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

6 Comments

Is it because of some fields having null values??
We change every null value to 0 (o.HMax3 ?? 0), so that shouldn't be the problem. As far as I can see, the only reason why there is no result should be because the query doesn't generate any records. You should try changing the select part with something like this: select new { Val = o.HMax1 }).ToList(), just to see whether your query returns anything. Also, just note that I didn't test this in linqpad. I just wrote a console app to test the query.
I just ran the query against the tables you provided in your post (ran the scripts to create the tables and insert the records), and I got a result, but commented out the o.Level expression from the where part. This was again a console app.
My original query (look at Question posted), return result (2 rows of record). Your query make a lot of sense. But why when running on my machine, it doesn't return anything???? Not sure how LINQPad works.Does it compiled based on Visual Studio installed in my machine? I'm using VS 2010, I believed C#4 compiler.....
OK, I see that I mixed the query a little bit. Sorry about that. Try again with the query above.
|
1

Try this:

(
 from o in MyTableFirst
 join p in MyTableSecond on o.TheName equals p.TheName
 where p.TheName == "CBA-123" && !p.Removed && 
 (o.Level ==100 || o.Level ==10)

 //combine all of the numbers into one list
 let listOfNumbers = new List<double?>{o.MaxValue1,o.MaxValue2,o.MaxValue3}

 //select the list
 select listOfNumbers
)
.SelectMany(c => c) //combine all the lists into one big list
.Max(c => c) //take the highst number
Improve this answer

10 Comments

Try run this query on LINQPad, it gave me this message: NotSupportedException: Parameterless aggregate operator 'Max' is not supported over projections.
@mADy1270 Mine works just fine (also in LinqPad) - make sure you have all the brackets set correctly. Edited my answer to make it more clear
I copied and pasted everything on my LINQPad. Still giving me the same message. I'm using LINQPad 4.43.06.
@mADy1270 me to. Make sure it is set to "Expression" if you cpoy/pasted directly from my code :)
Yup. It set to "Expression". Strange...Did I miss some other configuration?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.