[Solved] Linq NOT IN Join

[Steve]

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)

[Petko_I]

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

[Steve]

That worked. Thanks for the workaround. I would like to utilize as much LINQ as possible.

[Devanand Chahal]

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

 

[Petko_I]

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

[Edson]

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.

 

[Petko_I]

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