LINQ2Obj vs TSQL: GROUP BY and SUM

I tried to use Dynamic LINQ to create pretty ordinary TSQL query:
SELECT COL1 as B1, COL2 as B2, SUM(CONVERT(Money, COL3)) as B3
FROM a-table
GROUP BY COL1, COL2
HAVING(COL1 < 40)
a-table:
COL1  COL2  COL3

11    "12"  "13"

11    "12"  "23"
31    "32"  "33"
31    "32"  NULL
41    "42"  "43"

Expected result:
B1  B2   B3
11  "12" 36
31  "32" 33

It took me quite a long time to figure out how to create such a query in LINQ using a List of objects as source.
Here is first result of my struggles:
using System.Linq.Dynamic;
. . .
var res =
  list.AsQueryable().Where("COL1<40", null)

    .GroupBy("new(COL1 as B1,COL2 as B2)", "it")

    .Select("new(Key, Sum(COL3==null?0:double.Parse(COL3)) as B3)", null);

The "res" gives two records:
[0]    {Key={B1=11, B2=12}, B3=36}

[1]    {Key={B1=31, B2=32}, B3=33}

Then I need to "flatten" this result to produce what I really needed (but this is another task, not shown here)
[0]    {B1=11, B2=12, B3=36}

[1]    {B1=31, B2=32, B3=33}

Please let me know if anyone has a better solution.

LINQ and TSQL behavior difference

I found a lot, but for now record only this one.
When I say "LINQ" I mean LINQ-To-Object, not LINQ-To_Sql.
It took me quite a while to figure out why SQL request produces more records than LINQ request to preliminary loaded same data.
SQL:
SELECT aaa, bbb FROM TABLE WHERE bbb='xxx'; // returns 14 records
LINQ: I preloaded the whole TABLE into list, then query it:
var records = list.AsQueryable()
.Where("bbb==\"xxx\"", null)
.Select("new (aaa, bbb)", null); // returns 12 records

After all I found out that database contains 2 records with bbb='xxx     ' (spaces at the end)
TSQL ignores spaces, LINQ - does not, thus producing 2 less records.

LINQ to XML sample: get value with default (if not exists)

XElement dummy = new XElement("dummy");

if (xe.Descendants("Response").Descendants("Status").DefaultIfEmpty(dummy).First().Value == "ok"){}

LINQ to XML tiny sample

// load from file

XElement resp = XElement.Load("RCExtResponse.xml");

var all = from r in resp.Descendants()

          where r.Attributes().Count() == 0

          select r;

// or load from string

XElement xe = XElement.Parse(xmlString);

// use default

var caller =

    (from e in xe.Descendants("PracticeName")

     select (String)e).DefaultIfEmpty("");

Colors. Part 2.

Enumerate all Known Colors and put them in the dictionary

Dictionary<KnownColor, int> colorsRGB;

can be done this way:

int

    numAliceBlue = (int)KnownColor.AliceBlue,

    numYellowGreen = (int)KnownColor.YellowGreen,

    numColors = numYellowGreen - numAliceBlue + 1;

colorsRGB = new Dictionary<KnownColor, int>(numColors);

for (int i = numAliceBlue; i <= numYellowGreen; i++)

{

    KnownColor kc = (KnownColor)i;

    colorsRGB.Add(kc, Color.FromKnownColor(kc).ToArgb());

}

Then I can sort the collection using LINQ:

Sort by name:

var sortedColors = (from clr in colorsRGB

                    orderby clr.Key

                    select clr).ToArray();
by RGB value:

var sortedColors = (from clr in colorsRGB

                   orderby clr.Value descending

                   select clr).ToArray();

I created a function ColorWeight to “weight” a color (just sum of R, G, and B components),

And even can sort by the “weight”:

var sortedColors = (from clr in colorsRGB

                    orderby ColorWeight(clr.Key) descending

                    select clr).ToArray();

I do like LINQ!

For the test I draw buttons with KnownColor as background. I wanted to have foreground color to be recognizable on the background.

This turn out to be not easy task. I did not want to mess with non-RGB presentations (like HSV or HSL) which would simplify the task.

At first I tried “inverted” color:

return Color.FromArgb((int)backColor.ToArgb() ^ 0xF0F0F0);

It did not work well.

So I created “color weight”

private int ColorWeight(Color color)

{

    int bColor = (int)color.ToArgb(),

        r = (bColor & 0x00FF0000) >> 16,

        g = (bColor & 0x0000FF00) >> 8,

        b = bColor & 0x000000FF;

    return r + g + b;

}

It is not perfect, but kind of fit the purpose: after all I put Black text if “weight” > 0x60+0x60+0x60 and White text otherwise.

This is the result:

Download source code

LINQ. Sample. Sort list of IP addresses

In file “list.txt” I have one-per-line list of IP addresses that I’d like to sort.

I understand that it’s possible to do in many different ways.

But I’d like to illustrate use of LINQ for this.