Monday, October 20, 2008 7:56 PMHi all,
Assume I have the following tables:
id identifier Bit SignalID 1 100 0.1 1 2 100 0.2 1 3 100 0.3 1 4 100 0.4 1 5 100 0.5 1 6 100 0.6 1
SignalID SignalFormatID 1 1
SignalformatID Length 1 3
I want the following result: (i grouped from 0.1 - 0.3 and 0.4 - 0.6 cause the SignalFormat.Length = 3)
identifier Bit SignalID 100 0.1 - 0.3 1 100 0.4 - 0.6 1
So I want the signals grouped but according to a fix lenght, in this case 3.I need a linkstatment like the following:
from cm in DataContext.CANMessages
join signal in DataContext.Signals
on cm.SignalID equals signal.SignalID
join signalFormat in DataContext.SignalFormats
on signal.SignalFormatID equals signalFormat.SignalFormatID
groupy by cm.SignalID for SignalFormat.Length in this case 3
Don't know how to achieve the clustering!
Big thx and kind regards!
Tuesday, October 21, 2008 10:20 AM
From your sample input and output data I'm guessing you want sometihng like this:Code Snippetfrom sf in dc.SignalFormats
group sf by new
divGroup = Math.Round((sf.Bit + .1M) / 3, 1, MidpointRounding.AwayFromZero),
} into sfg
minBit = sfg.Min(mn => mn.Bit),
maxBit = sfg.Max(mx => mx.Bit),
...or expressed as TSQL:Code Snippet
select identifier, min(bit), max(bit), signal_id
group by round(([bit]+.1) /3, 1), identifier, signal_id
Edit note: I am assuming you're using Linq-to-SQL.
Don't try the linq expression above if you happen to be using Entity Framework as it will not work... EF can't translate most basic stuff from a linq expression into SQL, including (but not limited to) Math.Round... ...it will instead throw a runtime exception along the lines "LINQ to Entities does not recognize the method [xyz] method, and this method cannot be translated into a store expression."
L2S on the other hand does near perfect translations of most stuff I have thrown at it. One redundant wrapper in the example above but nothing that affects performance or the outcome of the query.
Tuesday, October 21, 2008 12:14 PMHallo Kristofer!
Big thx for your suggestion using an anonymous type and define "divGroup" to realize the grouping sounds like a good idea. I updated my problem description cause i have to do several joins but it should be doable as well.
And yes i am using linq to sql hope the generated t-sql is not so bad.
Tuesday, October 21, 2008 2:32 PM
The joins don't change anything - just add them to your query. (and any additional output columns either to the grouping or with the aggregate function of your choice)
The generated t-sql for the sample I posted above is fine. As I mentioned, L2S generates good SQL. EF is a different story...