0

We are having som performance issue with EF and I want to try rewrite a query to inline sql. However I am having some difficulties, it is probably just a noob issue.

Lets say I have 3 classes: License, GroupLicense and Product

    public class License
{
//stuff
Product MyProduct{get;set}
}

public class GroupLicense:License
{
// more stuff
}

public class Product
{
//product info stuff
}

Now I need to fetch some Grouplicenses depending on some requirements. However doing it with the datacontext and linq takes 2 minutes. Something similar to this

var institutionLicenses = db.GroupLicenses
            .Include(lic => lic.Product).Where(x => productIds.Contains(x.Product.Id) && x.LicenseStatus == StatusEnum.Active).ToList();

I want to do the same query using inline sql similar to this: I join the tables so all the primitive fields are okay.

 var gl = db.Database.SqlQuery<GroupLicense>("select * from GroupLicense as g left join Licenses on g.Id =Licenses.Id").ToList();

(It is just example code - I know it is not working :))

However when executing, The base product property on License is null, all the primitive fields are there.

What do I need to change in my sql statement to make it work?

4
  • I guess productIds may have a large size so that the query will become very slow. I don't think you can improve this much by using raw sql query. Technically you still need to build up the IN clause, pass in the list of productIds which I believe is also limited in size. Your sql query is in fact not some kind of equivalent to what the linq query does. I would stick to the linq query, if possible please let us know about the sizes of your entities (including the productIds). Commented Sep 18, 2015 at 12:46
  • Also, see this: stackoverflow.com/a/31773014/861716 Commented Sep 18, 2015 at 13:31
  • How many items does productIds contain? Commented Sep 18, 2015 at 19:11
  • It Can hold to a max of 50 Commented Sep 20, 2015 at 4:25

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.