help write left join with where clause

please help to write code that will return result of following sql expression

SELECT * FROM A
left join B on B.Id =A.bID and B.typeID = 0
Where ....

i trid using this code

var currentTypeID = 0;
var list = from a in A
               join b in B
               on new { first = a.bID, second = currentTypeID }
               equals new { first = b.Id, second = b.typeID }
               into tmp from b in tmp.DefaultIfEmpty()
               where ...
               select new {...}

but there error: "This expression type is not supported in LightSpeed 3.0"

   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression)
   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression)
   at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression)
   at Mindscape.LightSpeed.Linq.Sqo.GroupJoin.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression)
   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp)
   at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)
   at Mindscape.LightSpeed.Linq.Sqo.SelectMany.EvaluateMultipleArguments(ExpressionVisitor visitor, MethodCallExpression expression, Boolean isDefaultIfEmpty)

Use of constant values (e.g. currentTypeID in the example above) are not currently supported as join criteria, rather you can specify these as part of a where clause.

So to refactor what you have above:

var currentTypeID = 0;
var list = from a in A
               join b in B
               on a.bID equals b.Id
               into tmp from b in tmp.DefaultIfEmpty()
               where b.typeID == currentTypeID ...
               select new {...}

Jeremy

добрый день.
вариант запроса который вы предлагаете не эквивалентен тому что нужен мне.
пример
таблица A  - записи о неком событии
ID  = 1
bID  = 1

таблица Б  - дополнительная информация о событии
ID  = 1
typeID  = 1  -  определяет тип дополнительной информации
someValue = ''

задача выбрать все события из таблицы A,  и для событий у которых есть запись в таблице B с определённым типом, дополнительно выбрать информацию из таблицы B

запрос

SELECT * FROM A left join B on B.Id =A.bID and B.typeID = 1
вернёт
A.ID = 1,  A.bID = 1, B.ID=1, b.typeID =1

SELECT * FROM A left join B on B.Id =A.bID and B.typeID = 0
вернёт
A.ID = 1,  A.bID = 1, B.ID=null, b.typeID =null


если же воспользоваться тем запросом который предложен вами то результат будет другим


SELECT * FROM A left join B on B.Id =A.bID where  B.typeID = 1
вернёт
A.ID = 1,  A.bID = 1, B.ID=1, b.typeID =1

SELECT * FROM A left join B on B.Id =A.bID where B.typeID = 0
вернёт
null set (0 rows)


Есть ли какой то способ в lightSpeed написать именно тот запрос который мне нужен?


*****
sql request that you offer is not equivalent to what I need.
example
Table A - write about a event (has one record)
ID = 1,  bID = 1

Table B - Additional information about the event (has one record too)
ID = 1
typeID = 1 - determines the type of additional information
someValue =''

Task: to choose all the events in table A, and for events that have a record in table B with a certain type, in addition select the information from Table B

request

SELECT * FROM A left join B on B. Id = A.bID and B.typeID = 1
returns
A. ID = 1, A.bID = 1, B. ID = 1, b.typeID = 1

SELECT * FROM A left join B on B. Id = A.bID and B.typeID = 0
returns
A. ID = 1, A.bID = 1, B. ID = null, b.typeID = null


If, however, take advantage of the query that you suggested then the result will be different
SELECT * FROM A left join B on B. Id = A.bID where B.typeID = 1
returns
A. ID = 1, A.bID = 1, B. ID = 1, b.typeID = 1  - the same

SELECT * FROM A left join B on B. Id = A.bID where B.typeID = 0
returns
null set (0 rows)  - different


Is there a way to LightSpeed write exactly the query that I need?

No not currently as mentioned in the earlier post, although you could use a stored procedure as an escape hatch in this situation but thats not particularly ideal. I will have a look at whats involved in adding in support for this and let you know if this is something we can try and get in to a nightly sometime soon.

Jeremy

Just an additional point - you need to remember that LINQ is not SQL. It is an expression of your intent, and what you want returned, it is not an indication of the SQL that will be generated. This applies across the board for all LINQ providers that talk to a database (LINQ to SQL, Entity Framework, LightSpeed).

If you absolutely must have a specific query generated then you should write the SQL as you need it using options like Jeremy suggested. 

Yes, I understand that LINQ is not SQL. I will use the "escape hatch":)
Thanks for the quick response

One more thing. I tried to used other way and write the following code

 var list = from lw in UOW.Widgets
                 join lwm in UOW.WidgetModifications.Where(o => o.ProfileId == 1)
                        on lw.Id equals lwm.WidgetId
                 select new CustomWidget{...};
it take sql

SELECT    t0.WidgetID AS "t0.WidgetID",  ....,     t1.modificationID AS "t1.modificationID",  ...
FROM   widgets t0
INNER JOIN (SELECT  t0.modificationID, ...  FROM  widgetmodifications t0  WHERE   t0.ProfileId = 1 ) t1
ON t0.WidgetID = t1.WidgetId


it is very close to what i need, but i need LEFT JOIN.