I think this is one of those places that NULLs should be used in your table. Not sure if it will help you with this problem. But you already discovered the solution for that it seems. I found that if I don't use NULLs here SQL server has trouble with the Foreign key.
John
>Well.. I got part of this working. BUT, it is only returning those JobItems that *DO* have a part number. When the part_no is blank in the JobItem record, it will not include it in the returned set, I guess since it cannot find a corresponding parent in the Parts table. When the record has a part_no, it is doing the desired lookup just fine, but I also need to include the records that do not have a part_no, by using their own "local" description.
>
>Note that I am using Linq-to-SQL, and there is a parent-child Association between Parts table (parent) and JobItems table (child). Therefore, notice the hop to the lookup description (a.Part.desc) in the ternary clause a.part_no.Length == 0 ? a.desc : a.Part.desc
>
>I don't understand this behavior. Here is the Linq clause. The full c# code is shown
>
>
> public partial class JobItem
> {
> public IEnumerable<JobItemsUI> GetJobItemsByJobNoUI(string JobNo)
> {
> DataClasses1DataContext db = new DataClasses1DataContext();
>
> if (JobNo != null)
> {
> IEnumerable<JobItemsUI> JobItems = from a in db.JobItems
> where a.job_num == JobNo
> orderby a.item
> select new JobItemsUI (a.qty, a.part_no, a.dwg_no,
> a.part_no.Length == 0 ? a.desc : a.Part.desc,
> a.price);
> return JobItems;
> }
> else
> {
> return null;
> }
> }
>>
>
>
> public class JobItemsUI
> {
> public decimal qty { get; set; }
> public string part_no { get; set; }
> public string dwg_no { get; set; }
> public string desc { get; set; }
> public decimal price { get; set; }
>
> public JobItemsUI(decimal Qty, string PartNo, string DwgNo, string Desc, decimal Price)
> {
> qty = Qty;
> part_no = PartNo;
> dwg_no = DwgNo;
> desc = Desc;
> price = Price;
> }
> }
>
>
