CODEWITHSUNDEEP
c#asp.netdatabaselinq-to-entities
Bob Avallone
Bob Avallone

Reputation: 979

Linq to Entities To Column names

I am relatively new to Linq to Entities but I am making good progress. One item I haven’t been able to figure out yet is how to extract the columns names from the database. For example in the code below I would like to replace the constants with the field names from the database.

I haven't been able to get the answers I've seen eleswhere to work.

Any help will be appreciated.

Bob

DataTable dtNPRS = new DataTable();

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

try
{
    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));
    dtNPRS.Columns.Add(kActive,typeof(bool));
    dtNPRS.Columns.Add(kStatusId,typeof(Int32));

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

    return dtNPRS;
}

Upvotes: 2

Views: 2064

Answers (1)

Brian Mains
Brian Mains

Reputation: 50728

The only way to convert a LINQ entity to a DataTable dynamically is to use Reflection to get the names:

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

And a similar process for updating the rows:

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

EDIT: TO handle Nullable, do:

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

HTH.

Upvotes: 2

Related Questions