Linq NOT IN Join

7 posts, 1 answers
  1. Steve
    Steve avatar
    11 posts
    Member since:
    Jan 2010

    Posted 30 Aug 2010 Link to this post

    I am trying to do the Linq equivalent of SQL Server NOT IN. I continue getting an error. I have tried a thousand different combinations and am unable to get it to work. The unique situation is that the query contains a join. I have similar statement without a join and it works perfectly. Any assistance would be greatly appreciated.

    Code that does work:
    public IQueryable<V_SWGS_Sites_All> GetSWGSSitesVDelta(NERC_CIP_DomainModel context, string customerName)
    {
        IQueryable<V_SWGS_Sites_All> vSites = (from vs in context.V_SWGS_Sites_Alls
                               where vs.CustomerName == customerName
                               && !(from s in context.Sites
                               select s.SourceSiteId).Contains(vs.SiteID)
                               select vs);
        return vSites;
    }


    Code I am trying to impliment that does not work:
    public object GetCustomerContactDropDown(NERC_CIP_DomainModel context, int customerId, int siteId)
    {
        var custContact = (from cc in context.CustomerContacts
                    where cc.CustomerId == customerId
                    && !(from cs in context.CustomerSites
                         join csc in context.CustomerSiteContacts
                         on cs.CustomerSiteId equals csc.CustomerSiteId
                         where cs.SiteId == siteId
                         select csc.CustomerContactId).Contains(cc.CustomerContactId)
                  select new
                     {
                         cc.CustomerContactId,
                         name = cc.FirstName + " " + cc.LastName
                     });
        return custContact;
    }

    Below is the error message i am getting:

    Message: An exception occured during the execution of ' Extent.Where(cc => ((cc.CustomerId = value(Siemens.Energy.ASP.NERCCIP.Data.CustomerContact+<>c__DisplayClassf).customerId) && Not(value(Siemens.Energy.ASP.NERCCIP.Data.CustomerContact+<>c__DisplayClassf).context.CustomerSites.Join(value(Siemens.Energy.ASP.NERCCIP.Data.CustomerContact+<>c__DisplayClassf).context.CustomerSiteContacts, cs => cs.CustomerSiteId, csc => csc.CustomerSiteId, (cs, csc) => new <>f__AnonymousType4`2(cs = cs, csc = csc)).Where(<>h__TransparentIdentifierc => (<>h__TransparentIdentifierc.cs.SiteId = value(Siemens.Energy.ASP.NERCCIP.Data.CustomerContact+<>c__DisplayClassf).siteId)).Select(<>h__TransparentIdentifierc => <>h__TransparentIdentifierc.csc.CustomerContactId).Contains(cc.CustomerContactId)))).Select(cc => new <>f__AnonymousType3`2(CustomerContactId = cc.CustomerContactId, name = ((cc.FirstName + " ") + cc.LastName)))'. See InnerException for more details. .

    Source: Telerik.OpenAccess.35.Extensions.

    Stack Trace: at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues) at Telerik.OpenAccess.Query.ExpressionExecution.PerformDatabaseQuery[T](Piece`1 piece, Object[] grpVals) at Telerik.OpenAccess.Query.Piece`1.System.Collections.IEnumerable.GetEnumerator() at Telerik.Web.UI.ControlDataBinder.BindToEnumerableData(IEnumerable dataSource) at Telerik.Web.UI.ControlItemContainer.PerformDataBinding(IEnumerable data) at Telerik.Web.UI.RadComboBox.PerformDataBinding(IEnumerable dataSource) at Telerik.Web.UI.RadComboBox.OnDataSourceViewSelectCallback(IEnumerable data) at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) at Telerik.Web.UI.RadComboBox.OnDataBinding(EventArgs e) at Telerik.Web.UI.RadComboBox.PerformSelect() at System.Web.UI.WebControls.BaseDataBoundControl.DataBind() at Telerik.Web.UI.RadComboBox.DataBind() at SiteEdit.rg_Site_SelectedIndexChanged(Object sender, EventArgs e) in c:\Documents and Settings\helwi00s\My Documents\Visual Studio 2008\Projects\NERC_CIP\NERC_CIP\Pages\SiteEdit.aspx.cs:line 124

    Inner Exception: System.NullReferenceException: Object reference not set to an instance of an object. at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQueryImpl(Type type, Int32 elementAt, Object[] groupResolutionParamValues) at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues)

  2. Answer
    Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 02 Sep 2010 Link to this post

    Hello Steve,

    Our LINQ implementation spans quite a lot of features and we are constantly improving it. Yet, you have encountered a problem which we will investigate right away in order to expand the diversity of queries we support. By examining another ticket I see you have found a workaround by implementing a stored procedure which returns the desired result set. However, if you insist on using LINQ there is another workaround. The trick is to extract the inner select statement as a separate query, invoke the ToList() method so that you acquire the collection in memory and then proceed with your main query.

    var subselect = (from cs in context.CustomerSites
                         join csc in context.CustomerSiteContacts
                         on cs.CustomerSiteId equals csc.CustomerSiteId
                         where cs.SiteId == siteId
                         select csc.CustomerContactId).ToList();
     
    var custContact = (from cc in context.CustomerContacts
                    where cc.CustomerId == customerId
                    && !subselect.Contains(cc.CustomerContactId)
                  select new
                     {
                         cc.CustomerContactId,
                         name = cc.FirstName + " " + cc.LastName
                     });

    I hope this helps. Do not hesitate to contact us for further assistance.

    Greetings,
    Petko_I
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. DevCraft banner
  4. Steve
    Steve avatar
    11 posts
    Member since:
    Jan 2010

    Posted 03 Sep 2010 Link to this post

    That worked. Thanks for the workaround. I would like to utilize as much LINQ as possible.
  5. Devanand Chahal
    Devanand Chahal avatar
    26 posts
    Member since:
    Mar 2009

    Posted 09 Sep 2010 Link to this post

    Hi,

    I am facing a similar problem for IN Clause of te SQL.
    I am using the dynamic expressions to build the lambda expression for applying the IN clause .  I have used the code like below to generate the lambda expression.

     

     

     

     Expression.Call(ContainsMethodInfo, new Expression[] { rightExp, leftOp })

    ContainsMethodInfo:- It is the MethodInfo type object having the information regarding the information for "Contains" method of IEnumerable interface.
    rightExp:= this contains the expression for list of values for which data is searched
    leftOp := This is the expression for the field in which search to be made.

    My problem is that , it works for the long type of fields ,but not for String type of field.
    Let me know if more information is required.
    Thanks
    VIkas

     

  6. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 14 Sep 2010 Link to this post

    Hello Devanand Chahal,

    You have raised a very interesting topic which I would like to investigate efficiently. Let’s continue the discussion in your other thread.

    Greetings,
    Petko_I
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  7. Edson
    Edson avatar
    4 posts
    Member since:
    May 2009

    Posted 27 Sep 2010 Link to this post

    Hi, i am trying to get o relation with not in clause,
    I have two tables Functions and CapacityFunctios
    I get some samples that explain the relation like the following sample.

    var

     

     

    query = from ro in this.DataContext.Functions

     

     

     

    where !(from c in this.DataContext.CapacityFunctios select c.cdFunction).Contains (ro.cdFunction)

     

     

     

     

    select ro;

     

     

     

    return query;

    I am getting a error using this code.

    I have developed a Silverlight 4 application with Ria Services and Telerik ORM version 2010.2.714.1

    Thanks,

    Edson.

     

  8. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 30 Sep 2010 Link to this post

    Hello Edson,

    As far as I see you are trying to check whether a function object is not contained in a collection of persistent objects. The Contains method uses the Equals one to check whether an object is present in the collection. The Equals method itself when not overrided checks whether the references are the same. That is why such a query cannot be translated for execution on the database server. If you imagine how such a query looks in SQL you will see that depending on the definition of the Equals method different things need to be checked. Therefore, I would suggest rewriting the query so that ids are compared instead of objects. If you insist on using objects, then in-memory execution is the way to go, i.e. invoke ToList on all collections participating in the query.
    Let us know if this really is the case and contact us back if you need further assistance.

    All the best,
    Petko_I
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
Back to Top
DevCraft banner