Linq to sql and group by but only rows under a certain condition (having)

Question

Hi 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
select cm

Don't know how to achieve the clustering!

Big thx and kind regards!

Answer 1

From your sample input and output data I'm guessing you want sometihng like this:

Code Snippet

from sf in dc.SignalFormats
group sf by new
    {
        sf.Identifier,
        divGroup = Math.Round((sf.Bit + .1M) / 3, 1, MidpointRounding.AwayFromZero),
        sf.SignalID
    } into sfg
select new
    {
        sfg.Key.Identifier,
        minBit = sfg.Min(mn => mn.Bit),
        maxBit = sfg.Max(mx => mx.Bit),
        sfg.Key.SignalID
    }

 

 

 

...or expressed as TSQL:

 

Code Snippet

select identifier, min(bit), max(bit), signal_id
from signal_format
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.

Answer 2

Hallo 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.

greetings!

Answer 3

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...