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;
            }
        }