How to improve Query with linq

Question

I'm a little bit stuck, I took an new project but there is so timeout issue due to queries.

I don't know the syntax in linq to improve one querie even if i tried multiple times.

The query is :

var contactslist = (User as CustomPrincipal).contacts;

            var contacts = from m in db.ADDR_DEST.toList()
                           from n in contactslist
                           where m.ADDR_COUNTRY == n.country &&  m.ADDR_TPL_TYPE   == n.tpl_type
                           select m;

But I want to dosen't launch this query before that i had different parametre so i remove the .toList() for adding some condition with

contacts.where(..);

And then I would like launch my query but i got an error with the type which must be an list<> but in this case it's a Iqueryable.

Can you help me please? Is there an other way to launch this query after I do all my settings?

Do you have any relation between Addr_Dest to contactslist. If so, you can use .Include — sri harsha, Jan 24 '19 at 18:59
Please edit your question and show the code that throws the exception + the original exception message + type. The description isn't really clear. — Gert Arnold, Jan 25 '19 at 19:42

Peter Smith · Answer 1 · 2019-01-25T12:15:30.517

0

Try (not tested):

var contacts = db.ADDR_DEST.Where(m => m.COUNTRY == contactslist.country
                                  && m.ADDR_TPL_TYPE == contactslist.tpl_type)
                            .ToList();

If contactslist is a list then try the following

var contacts = (from addr in db.ADDR_DEST
                join cl in contactslist on
                   new {addr.COUNTRY, addr.ADDR_TPL_TYPE} equals new {cl.country, cl.tpl_type}
                select new {addr})
                .ToList();

The Linq join is easier to understand than its lambda equivalent!

There are some good examples here

edited Jan 25 '19 at 12:15

answered Jan 24 '19 at 18:36

Peter Smith

5,528
8
51
77

Thank you for your answer but contactlist is a list of contact so i don't have acces like that to the parametres country and tpl. How can I do for getting these in this instruction? – youness b Jan 25 '19 at 11:13
`var contactslist = (User as CustomPrincipal).contacts;` does not look like it would generate a list. Are you sure? – Peter Smith Jan 25 '19 at 11:46

score 0 · Answer 2 · answered Jan 24 '19 at 19:08

You can do something like that:

db.ADDR_DEST.Join(contactslist, 
                  addr => new { country = addr.ADDR_COUNTRY, type = addr.ADDR_TPL_TYPE }, 
                  cont => new { country = cont.country, type = cont.tpl_type }, 
                  (addr, cont) => addr)
            .ToList();

Then, if you want to choose the contactlist that meet certain requirements:

db.ADDR_DEST.Join(contactslist.Where(...).ToList(), 
                  addr => new { country = addr.ADDR_COUNTRY, type = addr.ADDR_TPL_TYPE }, 
                  cont => new { country = cont.country, type = cont.tpl_type }, 
                  (addr, cont) => addr)
            .ToList();

I hope you find it useful

First, Thank you for your answer. But it's doesnt work for me let me explain , In contactlist i have multiple pair of country and tpl and i want to put in my query like that in ADDR_DEST i got just the couple of value tpl & country from the table — youness b, Jan 25 '19 at 11:05

How to improve Query with linq

2 Answers2