So I am trying to order a query by an int var that is in a list of the same int vars; e.g. the query must be sorted by the lists order of items. Each datacontext is from a different database which is the reason i'm making the first query into an ordered
list of id's based on pet name order, only the pet id is available from the second query's data fields, Query looks like:
using (ListDataContext syndb = new ListDataContext())
{
using (QueryDataContext ledb = new QueryDataContext())
{
var stp = syndb.StoredPets.OrderBy(x => x.Name).Select(x => x.PetID).ToList();
var slp = ledb.SoldPets.OrderBy(x => stp.IndexOf(x.petId)).Select(x => x);
// do something with the query
}
}
The second query is giving me a "Method 'Int32 IndexOf(Int32)' has no supported translation to SQL." error, is there a way to do what I need?
I had tried just using a join between the 2 tables but they are on a different datacontext (and database for that matter), example below:
using (DataContext1 p1db = new DataContext1())
{
using (DataContext2 p2db = new DataContext2())
{
var p1 = p1db.GetPets();
var p2= from ps in p2db.PetsSold
join pl in p1 on ps.PetId equals pl.PetId
orderby ps.Name
select ps;
// do something with results
}
}
So this would work fine for a single datacontext, but they are on seperate datacontexts and I'm thrown the following error:
"The query contains references to items defined on a different data context."
The second query is giving me a "Method 'Int32 IndexOf(Int32)' has no supported translation to SQL." error, is there a way to do what I need?
HI,
This error means that not very standard sql statement can be converted to LINQ. So you can try this way by converting them into object collection in memory and have a try
by using AsEnumerble() method.
using (ListDataContext syndb = new ListDataContext())
{
using (QueryDataContext ledb = new QueryDataContext())
{
var stp = syndb.StoredPets.AsEnumerable().OrderBy(x => x.Name).Select(x => x.PetID).ToList();
var slp = ledb.SoldPets.AsEnumerable().OrderBy(x => stp.IndexOf(x.petId)).Select(x => x);
// do something with the query
}
}
JustJae
Member
5 Points
22 Posts
Order query by int var using an ordered list
Dec 11, 2012 03:13 AM|LINK
So I am trying to order a query by an int var that is in a list of the same int vars; e.g. the query must be sorted by the lists order of items. Each datacontext is from a different database which is the reason i'm making the first query into an ordered list of id's based on pet name order, only the pet id is available from the second query's data fields, Query looks like:
using (ListDataContext syndb = new ListDataContext()) { using (QueryDataContext ledb = new QueryDataContext()) { var stp = syndb.StoredPets.OrderBy(x => x.Name).Select(x => x.PetID).ToList(); var slp = ledb.SoldPets.OrderBy(x => stp.IndexOf(x.petId)).Select(x => x); // do something with the query } }The second query is giving me a "Method 'Int32 IndexOf(Int32)' has no supported translation to SQL." error, is there a way to do what I need?
thaicarrot
Contributor
5120 Points
1459 Posts
Re: Order query by int var using an ordered list
Dec 11, 2012 10:03 AM|LINK
you cannot do that except join the results then order by new result.
Weera
JustJae
Member
5 Points
22 Posts
Re: Order query by int var using an ordered list
Dec 12, 2012 12:25 AM|LINK
Okay thanks,
I had tried just using a join between the 2 tables but they are on a different datacontext (and database for that matter), example below:
using (DataContext1 p1db = new DataContext1()) { using (DataContext2 p2db = new DataContext2()) { var p1 = p1db.GetPets(); var p2= from ps in p2db.PetsSold join pl in p1 on ps.PetId equals pl.PetId orderby ps.Name select ps; // do something with results } }So this would work fine for a single datacontext, but they are on seperate datacontexts and I'm thrown the following error:
"The query contains references to items defined on a different data context."
Can anyone point me i the right direction please.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Order query by int var using an ordered list
Dec 12, 2012 11:14 AM|LINK
HI,
This error means that not very standard sql statement can be converted to LINQ. So you can try this way by converting them into object collection in memory and have a try by using AsEnumerble() method.
using (ListDataContext syndb = new ListDataContext()) { using (QueryDataContext ledb = new QueryDataContext()) { var stp = syndb.StoredPets.AsEnumerable().OrderBy(x => x.Name).Select(x => x.PetID).ToList(); var slp = ledb.SoldPets.AsEnumerable().OrderBy(x => stp.IndexOf(x.petId)).Select(x => x); // do something with the query } }