The second Linq query I showed pulls the description from the Parts table by way of an parent-child Association in the dbml between Parts and JobItems .. notice the "a.Part.desc"
Because of this, it thre resulting structure will not "fit" into the basic JobItem object class structure from the linq-to-sql dbml. That's why I had to make my own UI-version for this. I tried many ways around it, but I could not figure out any other way.
If you know a better way, please let me know.
Here it is again...
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.desc, a.price);
return JobItems;"a" is the JobItem, and "a.Part.desc" is the description field from the parent Parts table for the current JobItem.
(The Association in the dbml keeps you from having to use a Join in your Linq clause, but it does generate a Join in the SQL that it generates)
I did not add Attributes on my ad-hoc UI class (don't need them for the current use of display only), but they are on the "real" JobItem class that the dmbl generates.
>Hi,
>
>I don't see where your JobItems collection is pulling data from more than one table.
>Also I don't see any attributes in the JobItemsUI class definition...
>Isn't it just a question of designing JobItemsUI so that 'DataContext.GetTable
' works?
>
>Are we talking at cross-purposes?
>Regards,
>Viv
>
>>I've got all the attributes and Associations in place. I used the Linq-to-Sql designer and it's all working fine. There are some challenges though when working with normalized data. The easy linq queries work fine as long as you pull the full and pure object class that is defined in your dbml when you build your linq clause like this:
>>
>>>> IEnumerable<JobItems> JobItems = from a in db.JobItems
>> where a.job_num == JobNo
>> orderby a.item
>> select a
>> return JobItems;
>>
>>
>>However, a challenge is created when you wish to create a view of a recordset that pulls columns from different tables to de-normalize the data. At that instance, in order to pass the collection around in code, you cannot use the demo-friendly anonymous types; rather, you have to model-up your own classes to hold the results, so you will be working with a typed data collection.
>>
>>>> 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.desc,a.price);
>> return JobItems;
>>
>>
>>The class:
>>>> public class JobItemsUI
>> /// This is a custom class used to represent a UI displayable JobItem collection
>> {
>> 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;
>> }
>> }
>>>>
>>
>>
>>
>>>Hi,
>>>I'm only going from what I've read but isn't the first step to use attributes to map your JobItemsUI class to the underlying table and fields. I don't know if the created JobItemsUI objects will be automatically attached to the datacontext but if not it should be possible to do that in code.
>>>Regards,
>>>Viv
