How to get look up column value with ID?

Hi guys,

Recently we are facing problem that how to get/retrieve direct id of look up field from the list (for multi select and single select both).
For implementing this we are facing some challenge.

We are using SPQuery are fetching data from a list.

If the list contains Look up field then you observed that
1) If the look up is single select then it will show you the text value of the referenced field( means text of the field)

2) If the look up is multi select then it will show you text value and ID of that list with “;#” separated but the problem is it will not give you ID of first field.

Most of the time we are using this method

SPList spList = spWeb.Lists[strListName];
SPQuery spQuery = new SPQuery();
spQuery.Query = strQuery;
SPListItemCollection spItemCollection = spList.GetItems(spQuery);
if (spItemCollection.Count > 0)
{
dtResult = spItemCollection.GetDataTable();
}

But the return data table has the problem mention above.

So after checking a lot on the net we cannot find anything.
Then after checking with reflector we came to know that SPListItemCollection.GetDataTable() method will check the field type, if type is lookup or user then it will remove first ID and “;#” from the field result.

So sadly we cannot do anything directly with this method.

But we can find the ID;#Value by SPListItemCollection only

SPListItemCollection spItemCollection = spList.GetItems(spQuery);

String strLookUpValue = Convert.ToString(spItemCollection[0][“Look up field display name”]);


So this way you can find out what you are looking for.

Here is the snippet for the same.

SPList spList = spWeb.Lists[strListName];
SPQuery spQuery = new SPQuery();
spQuery.Query = strQuery;
SPListItemCollection spItemCollection = spList.GetItems(spQuery);
if (spItemCollection.Count > 0)
{
dtResult = spItemCollection.GetDataTable();
for (int i = 0; i < spItemCollection.Count; i++)
{
for(int j = 0;j < spItemCollection[i].Fields.Count;j++)
{
if (spItemCollection[i].Fields[j].Type == SPFieldType.Lookup && !spItemCollection[i].Fields[j].ReadOnlyField && !spItemCollection[i].Fields[j].Hidden)
{
dtResult.Rows[i][spItemCollection[i].Fields[j].InternalName] = Convert.ToString(spItemCollection[i][spItemCollection[i].Fields[j].Title]);
}
}

}
}

Please keep in mind this method will cause performance issue if your query returns bulk data but will be very usefull for specific query where data can be countable on fingers. :)