Combine 2 Linq result sets.

J

Jon

Hi Guys,

I'm reading from an XML file :

<item>
<Product1>1</Product1>
<Product2>2</Product2>
<Qty>4</Qty>
</item>
<item>
<Product1>3</Product1>
<Product2>4</Product2>
<Qty>5</Qty>
</item>
<item>
<Product1>2</Product1>
<Product2>2</Product2>
<Qty>6</Qty>
</item>
<item>
<Product1>4</Product1>
<Product2>1</Product2>
<Qty>7</Qty>
</item>

I'm trying to get the total for each product id, currently I am using 2
seperate queries..

var p1 = from p in xmlfile.Elements("item")
group p by p.Product1 into g
select new
{
id = g.Key,
qty = g.Sum(o => o.Qty)
};

var p2 = from p in xmlfile.Elements("item")
group p by p.Product2 into g
select new
{
id = g.Key,
qty = g.Sum(o => o.Qty)
};

This doesn't total the product correctly because the id can be in either
product1 or product 2

What's the best way to combine the results?

Is there a query that I can use to combine on the fly? Or do I need to
iterate through and combine manually?
 
P

Pavel Minaev

I'm reading from an XML file :

<item>
 <Product1>1</Product1>
 <Product2>2</Product2>
 <Qty>4</Qty>
</item>
<item>
 <Product1>3</Product1>
 <Product2>4</Product2>
 <Qty>5</Qty>
</item>
<item>
 <Product1>2</Product1>
 <Product2>2</Product2>
 <Qty>6</Qty>
</item>
<item>
 <Product1>4</Product1>
 <Product2>1</Product2>
 <Qty>7</Qty>
</item>

I'm trying to get the total for each product id, currently I am using 2
seperate queries..

            var p1 = from p in xmlfile.Elements("item")
                     group p by p.Product1 into g
                     select new
                     {
                         id = g.Key,
                         qty = g.Sum(o => o..Qty)
                     };

            var p2 = from p in xmlfile.Elements("item")
                     group p by p.Product2 into g
                     select new
                     {
                         id = g.Key,
                         qty = g.Sum(o => o..Qty)
                     };

This doesn't total the product correctly because the id can be in either
product1 or product 2

What's the best way to combine the results?
Click to expand...

It is not obvious from your example what exactly you are trying to do.
What do you mean by "total the product"? Do you want to get the
overall quantity of all products? Or, for any given N, the combined
quantity of all items where that N appears either in Product1 or in
Product2 element?

Either way, you can't do it when all you have is the results of the
two queries, because by that time you've lost information (about where
the sets intersect). You really need to do another query on the
original XML source.
 
J

Jon

I'm reading from an XML file :

<item>
<Product1>1</Product1>
<Product2>2</Product2>
<Qty>4</Qty>
</item>
<item>
<Product1>3</Product1>
<Product2>4</Product2>
<Qty>5</Qty>
</item>
<item>
<Product1>2</Product1>
<Product2>2</Product2>
<Qty>6</Qty>
</item>
<item>
<Product1>4</Product1>
<Product2>1</Product2>
<Qty>7</Qty>
</item>

I'm trying to get the total for each product id, currently I am using 2
seperate queries..

var p1 = from p in xmlfile.Elements("item")
group p by p.Product1 into g
select new
{
id = g.Key,
qty = g.Sum(o => o.Qty)
};

var p2 = from p in xmlfile.Elements("item")
group p by p.Product2 into g
select new
{
id = g.Key,
qty = g.Sum(o => o.Qty)
};

This doesn't total the product correctly because the id can be in either
product1 or product 2

What's the best way to combine the results?
Click to expand...
It is not obvious from your example what exactly you are trying to do.
What do you mean by "total the product"? Do you want to get the
overall quantity of all products? Or, for any given N, the combined
quantity of all items where that N appears either in Product1 or in
Product2 element?

Either way, you can't do it when all you have is the results of the
two queries, because by that time you've lost information (about where
the sets intersect). You really need to do another query on the
original XML source.
Click to expand...


ok, the items are a tube - Qty x Product1 is inserted in the left hand side
of the tube, Qty x Product2 is inserted in the right hand side of the tube.

I'm trying to find the total number of each product used.

<item>
<Product1>001</Product1>
<Product2>002</Product2>
<Qty>4</Qty>
</item>

means there are 4 x 001 and 4 x 002

<item>
<Product1>002</Product1>
<Product2>002</Product2>
<Qty>6</Qty>
</item>

means there are 6 x 002 + 6 x 002 which equals 12 x 002

so far we have 4 x 001, and 16 x 002

<item>
<Product1>004</Product1>
<Product2>001</Product2>
<Qty>7</Qty>
</item>

means there are 7 x 004 and 7 x 001

so we have 11 x 001, 16 x 002 and 7 x 004


I haven't been able to work out how to pull out that information in one
query.
 
M

Michael C

Jon said:
I'm trying to get the total for each product id, currently I am using 2
seperate queries..

var p1 = from p in xmlfile.Elements("item")
group p by p.Product1 into g
select new
{
id = g.Key,
qty = g.Sum(o => o.Qty)
};

var p2 = from p in xmlfile.Elements("item")
group p by p.Product2 into g
select new
{
id = g.Key,
qty = g.Sum(o => o.Qty)
};

This doesn't total the product correctly because the id can be in either
product1 or product 2

What's the best way to combine the results?

Is there a query that I can use to combine on the fly? Or do I need to
iterate through and combine manually?
Click to expand...

First thing I would say, and this is only my opinion, but abandon that style
of linq expression. It's much better, imo, to stick with the => style
throughout. Anyway, here's how you could do it:

xmlfile.Elements("item").Select(i => new { product = i["Product1"], Qty =
i["Qty"] })
..Concat(xmlfile.Elements("item").Select(i => new { product = i["Product2"],
Qty = i["Qty"] })
..GroupBy(i => i.product).Select(g => new { g.product, g.Sum(i => i.Qty) })

I'm not sure of the syntax to get Product1, Product2 and Qty out of the xml
so i used i["Qty"] etc so please replace this as you see fit.

Cheers,
Michael
 
P

Pavel Minaev

First thing I would say, and this is only my opinion, but abandon that style
of linq expression. It's much better, imo, to stick with the => style
throughout.
Click to expand...

I would strongly disagree with that. Compare a sugared query with
"let" to its equivalent with Select, or a "join" (esp. with several
sequences) with all the SelectMany that you'll need, and you'll
quickly see that the syntactic sugar is there for a purpose.
 
M

Michael C

Pavel Minaev said:
I would strongly disagree with that. Compare a sugared query with
"let" to its equivalent with Select, or a "join" (esp. with several
sequences) with all the SelectMany that you'll need, and you'll
quickly see that the syntactic sugar is there for a purpose.
Click to expand...

I've done some pretty complex linq with the => syntax and much prefer it. I
find it is actually clearer what is going on because you don't have to
wonder how it is going to be interpretted as everything executes from left
to right. I've written a lot of my own extension methods and you can't use
them with the "sugar" method. At first when I started using linq I tried to
use it where possible but found it annoying when I ran into a limitation and
had to use one of my own functions so had to convert the entire thing. For
example, with the query that Jon has I would use a function I called
SelectMultiple like this

xmlfile.Elements("item")
..SelectMultiple(i => new { prod = i.Product1, = i.qty }, new { prod =
i.Product2, i.Qty })
..GroupBy(i => i.Prod).Select(g => new { Prod = g.Key, Qty = g.Sum(i =>
i.Qty) });

This syntax maintains an oop style syntax, is more powerful, more consistant
and quite often shorter to write. When using the => syntax you never need to
resort to using the sugary syntax because you can do everything with =>.
However the sugary syntax is limited and quite often needs to resort to
using lambdas.

I could be wrong about this but I don't think you can pass your own delegate
into the sugary syntax, eg:

Func<int, bool> func = (i => (i & 1) == 0);
IEnumable<int> res = myCollection.Where(func);

Michael
 
D

dunawayc

Hi Guys,

I'm reading from an XML file :

<item>
<Product1>1</Product1>
<Product2>2</Product2>
<Qty>4</Qty>
</item>
<item>
<Product1>3</Product1>
<Product2>4</Product2>
<Qty>5</Qty>
</item>
<item>
<Product1>2</Product1>
<Product2>2</Product2>
<Qty>6</Qty>
</item>
<item>
<Product1>4</Product1>
<Product2>1</Product2>
<Qty>7</Qty>
</item>

I'm trying to get the total for each product id, currently I am using 2
seperate queries..

var p1 = from p in xmlfile.Elements("item")
group p by p.Product1 into g
select new
{
id = g.Key,
qty = g.Sum(o => o.Qty)
};

var p2 = from p in xmlfile.Elements("item")
group p by p.Product2 into g
select new
{
id = g.Key,
qty = g.Sum(o => o.Qty)
};

This doesn't total the product correctly because the id can be in either
product1 or product 2

What's the best way to combine the results?

Is there a query that I can use to combine on the fly? Or do I need to
iterate through and combine manually?
Click to expand...

Hi Guys,

I'm reading from an XML file :

<item>
<Product1>1</Product1>
<Product2>2</Product2>
<Qty>4</Qty>
</item>
<item>
<Product1>3</Product1>
<Product2>4</Product2>
<Qty>5</Qty>
</item>
<item>
<Product1>2</Product1>
<Product2>2</Product2>
<Qty>6</Qty>
</item>
<item>
<Product1>4</Product1>
<Product2>1</Product2>
<Qty>7</Qty>
</item>

I'm trying to get the total for each product id, currently I am using 2
seperate queries..

var p1 = from p in xmlfile.Elements("item")
group p by p.Product1 into g
select new
{
id = g.Key,
qty = g.Sum(o => o.Qty)
};

var p2 = from p in xmlfile.Elements("item")
group p by p.Product2 into g
select new
{
id = g.Key,
qty = g.Sum(o => o.Qty)
};

This doesn't total the product correctly because the id can be in either
product1 or product 2

What's the best way to combine the results?

Is there a query that I can use to combine on the fly? Or do I need to
iterate through and combine manually?
Click to expand...

Does this example give you the required result? I'm still learning
the ins and outs of linq so there may be a more concise way to do
this. The Convert.ToInt32 call may fail if the element is empty so
that is something you will have to take into consideration. Also, the
let operators are not necessary. You can just put the Convert lines
directly in the select new clause, but to me using let can be a little
easier to understand what is going on.

static void Main(string[] args) {

var root = new XElement("items",
new XElement("item",
new XElement("product1", "1"),
new XElement("product2", "2"),
new XElement("qty", "4")),
new XElement("item",
new XElement("product1", "3"),
new XElement("product2", "4"),
new XElement("qty", "4")),
new XElement("item",
new XElement("product1", "2"),
new XElement("product2", "2"),
new XElement("qty", "4")),
new XElement("item",
new XElement("product1", "4"),
new XElement("product2", "1"),
new XElement("qty", "4")));

var items = from item in root.Elements()
let Product1Qty = Convert.ToInt32(item.Element
("product1").Value) * Convert.ToInt32(item.Element("qty").Value)
let Product2Qty = Convert.ToInt32(item.Element
("product2").Value) * Convert.ToInt32(item.Element("qty").Value)
select new { Product1 = Product1Qty, Product2 =
Product2Qty };

var totals = new { Product1Total = items.Sum(p1 => p1.Product1),
Product2Total = items.Sum(p2 => p2.Product2) };

Console.WriteLine("Product 1 total: {0}, Product 2 total: {1}",
totals.Product1Total, totals.Product2Total);
Console.ReadLine();
}

Chris
 
J

Jon

Michael C said:
For example, with the query that Jon has I would use a function I called
SelectMultiple like this

xmlfile.Elements("item")
.SelectMultiple(i => new { prod = i.Product1, = i.qty }, new { prod =
i.Product2, i.Qty })
.GroupBy(i => i.Prod).Select(g => new { Prod = g.Key, Qty = g.Sum(i =>
i.Qty) });
Click to expand...

Hi Michael,

The solution provided in your first post did what I wanted. I do find it
more difficult to read code when using the lambda syntax, but I guess that
comes down to practice.

Is there any chance you could share how you would code your SelectMultiple
function?
 
M

Michael C

Jon said:
Hi Michael,

The solution provided in your first post did what I wanted. I do find it
more difficult to read code when using the lambda syntax, but I guess that
comes down to practice.

Is there any chance you could share how you would code your SelectMultiple
function?
Click to expand...

Yep no worries, I don't have vs2008 here so there might be a couple of minor
fixes required:

public static IEnumerable<U> SelectMultiple<T, U>(this IEnumerable<T>
source, Func<T, U> selector1, Func<T, U> selector2)
{
foreach(T item in source)
{
yield return selector1(item);
yield return selector2(item);
}
}

you should probably add a check to see if source, selector1 and selector2
are null and raise an error. I also created overloads for SelectMultiple
with 3 and 4 functions.

Regards,
Michael
 
P

Pavel Minaev

Yep no worries, I don't have vs2008 here so there might be a couple of minor
fixes required:

public static IEnumerable<U> SelectMultiple<T, U>(this IEnumerable<T>
source, Func<T, U> selector1, Func<T, U> selector2)
{
    foreach(T item in source)
   {
        yield return selector1(item);
        yield return selector2(item);
   }

}
Click to expand...

Isn't this what SelectMany is normally used for?

seq.SelectMany(x => new[] { selector1(x), selector2(x) });
 
M

Michael C

Pavel Minaev said:
Isn't this what SelectMany is normally used for?
seq.SelectMany(x => new[] { selector1(x), selector2(x) });
Click to expand...

You can use SelectMany if you like, you can use Concat. SelectMultiple is
just another way to do it. It avoids the need to create an array for every
row.

Michael
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

XML to Linq and Optional fields 3
Vlookup, can this be done? 1
Anonymous Types, LINQ and Generics 3
Creating a Lookup Table Using Do...Until Loop 2
calculations, javascript + asp 4
cannot install Vista ACPI error 6
Grouping and Outlining 1
Can't print OL2003 4

Top