I'm relatively recent to Linq to Organizations however i am making good progress. One item I haven’t had the opportunity to determine yet is how you can extract the posts names in the database. For instance within the code below I must replace the constants using the area names in the database.

I've not been capable of getting the solutions I have seen eleswhere to operate.

Any assistance will be appreciated.


DataTable dtNPRS = new DataTable();

const string kInitDate = "NPR_Init_Date";
const string kActive = "Active";
const string kStatusId = "NPR_Status_Id";

    DataRow drNPR;

    var nprs = (from n in db.FMCSA_NPR
                join u in db.FMCSA_USER  on n.CREATED_BY equals u.ID
                join t in db.LKUP_NPR_TYPE  on  n.NPR_TYPE_ID equals t.ID
                join s in  db.LKUP_AUDIT_STATUS on  n.NPR_STATUS_ID equals s.ID
                where n.ROLE_ID == pRoleId && n.OWNER_ID == pOwnerId
                      && n.NPR_STATUS_ID == pNPRStatusId && n.ACTIVE == pActive
                select n).ToList();

    if (nprs.Count() == 0)
       return null;

    //  / build the table structure we need
    dtNPRS.Columns.Add(kInitDate, typeof(DateTime));

    foreach (var npr in nprs)
       drNPR = dtNPRS.NewRow();
       drNPR[kInitDate] = npr.NPR_INIT_DATE;
       drNPR[kActive] = npr.ACTIVE;
       drNPR[kStatusId] = npr.NPR_STATUS_ID;

    return dtNPRS;

The only method to convert a LINQ entity to some DataTable dynamically is by using Reflection to obtain the names:

var props = typeof(FMCSA_NPR).GetProperties();
foreach (var prop in props)
    dtNPRS.Columns.Add(prop.Name, prop.PropertyType);

Along with a similar process for upgrading the rows:

foreach (var prop in props)
    dtNPR[prop.Name] = prop.GetValue(npr, null);

EDIT: To deal with Nullable, do:

if (prop.PropertyType.Equals(typeof(Nullable<>)))
    var type = prop.PropertyType.GetGenericArguments()[0];