How to use two variables in a foreach loop?

Question

I am using dotConnect linq to sqlite. What i want to do is use two variable in a foreach loop. Followed this code but it is not working. This is my code snippet.

    bool check_units(int id)
    {
        MainDataContext medic = new MainDataContext();
        bool check = false;
        var medic_query = from m in medic.Medicines
                          orderby m.Id
                          where m.Id == id
                          select m;

        var invo_query = from inv in medic.Invoices
                         orderby inv.Id
                         where inv.Id == id
                         select inv;

        var med_inv = medic_query.Zip(invo_query, (m, i) => new { Medicine = m, Invoice = i });

        foreach(var mi in med_inv)
        {
            if (mi.Medicine.UNIT > mi.Invoice.UNIT)
            {
                mi.Medicine.UNIT -= mi.Invoice.UNIT;
                if (mi.Medicine.UNIT < 10)
                {
                    MessageBox.Show(mi.Medicine.Name + " is short in Invertory!\nUnits Remaining: " + mi.Medicine.UNIT,
                        "Inventory Empty", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
                chk = true;
            }
            else
            {
                MessageBox.Show("Not Enough Stock!\nUnits Remaining: " + mi.Medicine.UNIT,
                    "Inventory Short", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        medic.SubmitChanges();
        return chk;
    }

The Problem that i am facing is my code gives an error

The query operator 'Zip' is not supported.

There is no kind of syntax error or warning. I think that Zip operator can not be used with linqtosql type of queries!

Waiting for support! Thanks :)

D Stanley · Accepted Answer · 2013-08-15T18:05:15.100

3

The problem is the underlying query provider cannot translate the Zip method to raw SQL. Since you're not applying any additional filters the simplest method is to hydrate the query using AsEnumerable:

    var med_inv = medic_query.AsEnumerable()
                             .Zip(invo_query, (m, i) => new { Medicine = m, Invoice = i });

Are you certain that the records from each query line up properly? This looks like it should be a Join but since you don't specify if/how the records are related we can't tell what the proper join would be.

edited Aug 15 '13 at 18:05

answered Aug 15 '13 at 17:52

D Stanley

149,601
11
178
240

Thanks Did what you suggested but now the control is not entering the foreach loop! – Muneeb Mirza Aug 15 '13 at 18:53
Actually both the tables are same! Difference is Table Medicine is the Inventory and the other one is for invoice record! This snippet was my Bad Logic, now i will modify my code that would require only one query :p – Muneeb Mirza Aug 15 '13 at 19:04
Though i am going to change my code, still i might need this in future. Why is this code not working or all the other suggestions? – Muneeb Mirza Aug 15 '13 at 19:06

score 2 · Answer 2 · answered Aug 15 '13 at 17:53

2

The problem that you see has to do with Zip method not being supported by the IQueryable<T> provider. Forcing the move of your data to memory should fix this problem - you can use AsEnumerable to do the cast, or bring the data in memory explicitly, like this:

var medList = medic_query.ToList();
var invoList = invo_query.ToList();
var med_inv = medList.Zip(invoList, (m, i) => new { Medicine = m, Invoice = i });

This shouldn't have much impact on the performance, because Zip is going to bring both lists into memory anyway.

answered Aug 15 '13 at 17:53

Sergey Kalinichenko

714,442
84
1,110
1,523

The key here is that you pull the two querys down individually, so you do 2 DB round trips, rather than merging them into one query (in theory, one could imagine a `Zip` implementation that put both into a single batch). I'm not sure if there is any other way of batching the query queries using that query provider. – Servy Aug 15 '13 at 18:12
@Servy I agree - if the query provider cannot do it, you need to run `Zip` in memory. – Sergey Kalinichenko Aug 15 '13 at 18:14
Thanks Did what you suggested but now the control is not entering the foreach loop! Same as the other suggestion :( – Muneeb Mirza Aug 15 '13 at 18:56
@MuneebMirza Check `medList.Count` then: it is zero. – Sergey Kalinichenko Aug 15 '13 at 18:59
@dasblinkenlight: Thanks Sir! You helped me alot. now i got what mistake i had made! – Muneeb Mirza Aug 15 '13 at 19:30

score 2 · Answer 3 · answered Aug 15 '13 at 17:53

2

Add AsEnumerable() calls:

var med_inv = medic_query.AsEnumerable().Zip(invo_query.AsEnumerable(), (m, i) => new { Medicine = m, Invoice = i });

answered Aug 15 '13 at 17:53

MarcinJuraszek

124,003
15
196
263

Thanks Did what you suggested but now the control is not entering the foreach loop! Same as the other suggestion :( – Muneeb Mirza Aug 15 '13 at 18:58
That's because your `medic_query` and `invo_query` don't return any items from database. – MarcinJuraszek Aug 15 '13 at 19:11
Thanks Sir! You helped me alot. now i got what mistake i had made! – Muneeb Mirza Aug 15 '13 at 19:31

How to use two variables in a foreach loop?

3 Answers3