var facetList = (from facet in MetaData.Facet
                        where facet.RootFacetId == (from facet1 in MetaData.Facet
                                                            where facet1.Id == facetId
                                                            select facet1.RootFacetId).First()
                        select facet).ToList();

RootFacetId is a nullable int. The generated sql is:

SELECT [LPLA_1].[id] AS [Id], [LPLA_1].[parentFacetId] AS [ParentFacetId], [LPLA_1].[rootFacetId] AS [RootFacetId], [LPLA_1].[name] AS [Name] FROM [gifter-dev].[dbo].[Facets] [LPLA_1]  WHERE ( ( ( ( [LPLA_1].[rootFacetId] IS NULL))))

I've verified that the facetId is not actually null. I've tried various cast operations, but no luck. Can anyone see what I'm missing here

In case it's helpful, here's the table structure:

CREATE TABLE [dbo].[Facets](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [parentFacetId] [int] NULL,
    [rootFacetId] [int] NULL,
    [name] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON

Thanks all.

I've verified that the facetId is not actually null. I've tried various cast operations, but no luck.

Ok. The facetId is not null, but Does the subquery return any result?

Yes. If I write the subquery by itself, I do get the expected result. Thanks.

runtimelib version, etc. etc. etc. etc.

Version: 2.6.9.220 Trace output:

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[gifter.api.model.EntityClasses.FacetEntity]).Where(facet => (facet.Id = value(gifter.repository.FacetService+<>c__DisplayClass6).facetId)).Select(facet => facet.RootFacetId).First()
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Enter: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.CloseConnection
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[gifter.api.model.EntityClasses.FacetEntity]).Where(facet => (facet.RootFacetId = value(gifter.repository.FacetService).MetaData.Facet.Where(f => (f.Id = value(gifter.repository.FacetService+<>c__DisplayClass6).facetId)).Select(f => f.RootFacetId).First()))
Method Enter: DataAccessAdapterBase.FetchEntityCollection(8)
Method Enter: DataAccessAdapterBase.FetchEntityCollectionInternal(7)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
Method Exit: DataAccessAdapterBase.FetchEntityCollectionInternal(7)
Method Enter: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.FetchEntityCollection(8)
A first chance exception of type 'System.NullReferenceException' occurred in gifter.repository.DLL

There are 2 expressions here. The first works. The full method is:

public FacetEntity GetFacetTreeContainingFacetId(int facetId)
        {
            var selectedFacet = (from facet in MetaData.Facet
                                  where facet.Id == facetId
                                  select facet.RootFacetId).First();
            var facetListTree = (from facet in MetaData.Facet
                                        where facet.RootFacetId == (from f in MetaData.Facet
                                                                             where f.Id == facetId
                                                                             select f.RootFacetId).First()
                                        select facet).ToList();

            var rootFacet1 = facetListTree.IsNullOrEmpty()
                                    ? null : (from facet in facetListTree 
                                                 where facet.Id == facetListTree[0].RootFacetId 
                                                 select facet).First();
            BuildTree(rootFacet1, facetListTree);
            return rootFacet1;
        }

We're looking at a tree node here, where the relevant fields are:

id - autogenerated id parentId - the parent of the current node rootId - the id of the rootnode of the tree

So this method accepts any node in the tree, and returns the root node of that tree, w/ all of the children populated.

There is no inheritance involved at this point.

Sorry for omitting this stuff from the beginning Frans, I should have read that post. That said, you may avoid some pain, if you name it something like: "REQUIRED INFORMATION FOR SUPPORT". Just a thought.

Thanks

I'll look into it. Thanks for the info

Btw, about the info request: when you start a new thread, there's a list with items under 'IMPORTANT', above the textbox where you type in text .

Ah, well so much for my reading comprehension skills.. :-/ Thanks Frans.

I indeed see the same behavior:

int employeeid = 3;
var siblings = from e in metaData.Employee
               where e.ReportsTo == (from m in metaData.Employee where m.EmployeeId==employeeid select m.ReportsTo.Value).First()
               select e;

Even not specifying .Value on ReportsTo gives the same IS NULL predicate instead of the expected scalar query.

To work around this, you could fetch the parent of the facet of which you pass the id in, and pass that as a value to compare with in the where (the 'selectedFacet' query isn't executed btw), as your query is fetching all siblings of the facet with id specified (and I'm also not sure if the root selection you do at the end is correct in all cases.) and not a whole tree.

I'll look into why this happens and see if I can find the cause why the scalar query isn't present in the where...

(edit) Debugging shows that the binaryexpression converter method has no support for a full query on either side of the operator.

I've to add this code and hope to have a proper working provider for you on tuesday.

Fixed.

please see the attached dll for the fix.