I am trying to accomplish a Left outer Join with

Dim meta As New Linq.LinqMetaData

    Dim vend = From v In meta.VwVendorActiveDb
               Group Join m In meta.MacPubvenmap On v.VendNo Equals m.VendNo Into mylist = Group _
               From m In mylist.DefaultIfEmpty()
               Select v.VendNo, v.VendName

However I keep getting the error

• Thrown: "Unexpected expression type 'Unknown' found as source of DefaultIfEmpty(). Can't continue" (SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException) Exception Message = "Unexpected expression type 'Unknown' found as source of DefaultIfEmpty(). Can't continue", Exception Type = "SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException"

How do I accomplish a left outer join...honestly it doesnt have to be linq perfered...thanks

Edit -

My SQL is

SELECT a.* FROM vwVendor a LEFT OUTER JOIN dbo.pubvenmap b on a.vend_no = b.vend_no where b.vend_no is null

stack trace

at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleJoinExpression(JoinExpression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\QueryExpressionBuilder.cs:line 2822

at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\GenericExpressionHandler.cs:line 180

at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\ExpressionHandlers\QueryExpressionBuilder.cs:line 141

at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 158

at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 92

at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 697

at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProQuery.cs:line 87

at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProQuery.cs:line 162

at System.Collections.Generic.List1..ctor(IEnumerable1 collection)

at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

at MacolaImportManager.frmVendorMapping.BindAllProducts() in C:\Projects\Office\MacolaImportManager\frmVendorMapping.vb:line 86

Hi

Please can you post the equivalent SQL that you are trying to generate ?

Thanks

Matt

I just updated my question with the query.

MTrinder wrote:

Hi

Please can you post the equivalent SQL that you are trying to generate ?

Thanks

Matt

Can someone please assist??? Is this how long prospective customers have to wait for a response?

skhan wrote:

I just updated my question with the query.

MTrinder wrote:

Hi

Please can you post the equivalent SQL that you are trying to generate ?

Thanks

Matt

it appears "Left Outer Join" does not work via LINQ in your framework...or perhaps i am missing something.

I also tried the following and get the same error. This is getting to be really frustrating.

    Dim products = From p In meta.VwProduct
                   Group Join m In meta.MacPubvenprodmap On p.ProdId Equals m.ProdId
                   Into Group
                   From m In Group.DefaultIfEmpty()
                    Select p

    grdAllProducts.DataSource = products.ToList()

All I am looking for is simple Left outer Joins where by I see all data in TableA EXCLUDING THOSE WHICH EXISTS BETWEEN TableA and TableB.

skhan wrote:

Can someone please assist??? Is this how long prospective customers have to wait for a response?

skhan wrote:

I just updated my question with the query.

MTrinder wrote:

Hi

Please can you post the equivalent SQL that you are trying to generate ?

Thanks

Matt

Hi there. Just clarifying. In your first post you said:

skhan wrote:

My SQL is

SELECT a.* FROM vwVendor a LEFT OUTER JOIN dbo.pubvenmap b on a.vend_no = b.vend_no where b.vend_no is null

And in your previous post you said:

skhan wrote:

All I am looking for is simple Left outer Joins where by I see all data in TableA EXCLUDING THOSE WHICH EXISTS BETWEEN TableA and TableB.

Are you sure the two statements are consistent? In your first post, you will get all data in TableA. Could you please post the real sql is working for you right now?

Also, in your first post:

Dim vend = From v In meta.VwVendorActiveDb
                 Group Join m In meta.MacPubvenmap On v.VendNo Equals m.VendNo Into mylist = Group _
                 From m In mylist.DefaultIfEmpty()
                 Select v.VendNo, v.VendName

However I keep getting the error

• Thrown: "Unexpected expression type 'Unknown' found as source of DefaultIfEmpty(). Can't continue" (SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException) Exception Message = "Unexpected expression type 'Unknown' found as source of DefaultIfEmpty(). Can't continue", Exception Type = "SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException"

The correct syntax is:

Dim vend = From v In meta.VwVendorActiveDb _
                  Join m In meta.MacPubvenmap On v.VendNo Equals m.VendNo Into mylist _
                 From x In mylist.DefaultIfEmpty()
                 Select ...

As I said above, in you have to think what you really want in the select part. If you are looking into a way to exclude the common records between A and B, Shouldn't it be like this?:

SELECT A.*
FROM TableA AS A
WHERE A.Id NOT IN
    ( SELECT B.AId FROM B )

... and in such case you can use the Contains LINQ2LLBL construct.

Thanks for replying. I am looking for a left outer join (see the where [columnName] = null)...everything in left column exclusing matching rows...anyways how would I then perform a NOT IN query?

daelmo wrote:

Also, in your first post:

Dim vend = From v In meta.VwVendorActiveDb
                 Group Join m In meta.MacPubvenmap On v.VendNo Equals m.VendNo Into mylist = Group _
                 From m In mylist.DefaultIfEmpty()
                 Select v.VendNo, v.VendName

However I keep getting the error

• Thrown: "Unexpected expression type 'Unknown' found as source of DefaultIfEmpty(). Can't continue" (SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException) Exception Message = "Unexpected expression type 'Unknown' found as source of DefaultIfEmpty(). Can't continue", Exception Type = "SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException"

The correct syntax is:

Dim vend = From v In meta.VwVendorActiveDb _
                  Join m In meta.MacPubvenmap On v.VendNo Equals m.VendNo Into mylist _
                 From x In mylist.DefaultIfEmpty()
                 Select ...

As I said above, in you have to think what you really want in the select part. If you are looking into a way to exclude the common records between A and B, Shouldn't it be like this?:

SELECT A.*
FROM TableA AS A
WHERE A.Id NOT IN
    ( SELECT B.AId FROM B )

... and in such case you can use the Contains LINQ2LLBL construct.

I am trying to achiever the same SQL query in linq...I am in real need of a solution...how can I achieve the same in SelfServicing....I am new to LLBLGen.

daelmo wrote:

Hi there. Just clarifying. In your first post you said:

skhan wrote:

My SQL is

SELECT a.* FROM vwVendor a LEFT OUTER JOIN dbo.pubvenmap b on a.vend_no = b.vend_no where b.vend_no is null

And in your previous post you said:

skhan wrote:

All I am looking for is simple Left outer Joins where by I see all data in TableA EXCLUDING THOSE WHICH EXISTS BETWEEN TableA and TableB.

Are you sure the two statements are consistent? In your first post, you will get all data in TableA. Could you please post the real sql is working for you right now?

I am looking for a left outer join (see the where [columnName] = null)...everything in left column exclusing matching rows

I'm not sure I understad the above sentence.

(Edit)

select * from Customers
where customerId not in (select customerid from orders)

select * from Customers c
where not exists (select customerid from orders o where o.CustomerID = c.CustomerID)

The above 2 sql are similar, although the second one is faster.

And here is how to implement it in linq:

var q = from c in metaData.Customer
        where !(from o in metaData.Order
                select o.CustomerId)
                .Contains(c.CustomerId)
        select c;

or

var q = from c in metaData.Customer
        where !metaData.Order.Select(o => o.CustomerId).Contains(c.CustomerId)
         select c;