Use the creation time, creator, modification time, and modifier in LINQ to SharePoint.

Document directory

• SPMetal command
• Lost Column

LINQ to SharePoint is a new feature of SharePoint 2010, which is used to initiate queries on SharePoint servers. Unlike the classic CAML query, It queries list data through a strong object model and the LINQ query syntax.

SPMetal command

As mentioned in the previous article, the first step to using LINQ to SharePoint is to run the SPMetal tool to create an entity model for an existing SharePoint site. This tool is located in 14 \ BIN. Here is an example of how to use it:

SPMetal/web: http: // Our SharePoint site: Port/code: SiteEntities. cs

 
This command will create a C # code file named Model. cs under 14 \ BIN, which contains the entity Model. After adding this file to our project, we can use LINQ to SharePoint for query. For example, you can use the following server code to output all list items whose title length is at least three characters long in the "Manager" list:

Using (SiteEntitiesDataContext ctx = new SiteEntitiesDataContext ("http://moss.contoso.com/sites/Lab01") {StringBuilder output = new StringBuilder (); foreach (Manager project manager in ctx. manager. where (x => x. title. length> = 3) {output. appendLine (manager. name);} Console. writeLine (output. toString ());}

 

Lost Column

By default, the Creator, creation time, modifier, and modification time columns are not automatically created by SPMetal. However, the framework provides the ability to scale the LINQ to SharePoint provider, and can expand the object-relational ing system. In other words, once we have told LINQ to SharePoint how to retrieve and update these columns from the content database, these columns can be easily used in the future.

Next we will expand the basic entity class ("project" class) of the model and create a new C # code file (named "ModelExtensions. cs" Here "):

Using System; using System. collections. generic; using System. linq; using System. text; using Microsoft. sharePoint. linq; using Microsoft. sharePoint; public partial class project: ICustomMapping {[CustomMapping (Columns = new String [] {"Modified", "Created", "Editor", "Author"})] public void MapFrom (object listItem) {SPListItem item = (SPListItem) listItem; this. modified = (DateTime) item ["Modified"]; this. crea Ted = (DateTime) item ["Created"]; this. createdBy = (string) item ["Author"]; this. modifiedBy = (string) item ["Editor"];} public void MapTo (object listItem) {SPListItem item = (SPListItem) listItem; item ["Modified"] = this. modified; item ["Created"] = this. created; item ["Author"] = this. createdBy; item ["Editor"] = this. modifiedBy;} public void Resolve (RefreshMode mode, object originalListItem, object DatabaseObject) {SPListItem originalItem = (SPListItem) originalListItem; SPListItem databaseItem = (SPListItem) databaseObject; DateTime expiration = (DateTime) originalItem ["Modified"]; DateTime dbModifiedValue = (DateTime) databaseItem ["Modified"]; DateTime originalCreatedValue = (DateTime) originalItem ["Created"]; DateTime dbCreatedValue = (DateTime) databaseItem ["Created"]; string origi NalCreatedByValue = (string) originalItem ["Author"]; string dbCreatedByValue = (string) databaseItem ["Author"]; string originalModifiedByValue = (string) originalItem ["Editor"]; string dbModifiedByValue = (string) databaseItem ["Editor"]; if (mode = RefreshMode. overwriteCurrentValues) {this. modified = dbModifiedValue; this. created = dbCreatedValue; this. createdBy = dbCreatedByValue; this. modifiedBy = DbModifiedByValue;} else if (mode = RefreshMode. keepCurrentValues) {databaseItem ["Modified"] = this. modified; databaseItem ["Created"] = this. created; databaseItem ["Author"] = this. createdBy; databaseItem ["Editor"] = this. modifiedBy;} else if (mode = RefreshMode. keepChanges) {if (this. modified! = OriginalModifiedValue) {databaseItem ["Modified"] = this. Modified;} else if (this. Modified = originalModifiedValue & this. Modified! = DbModifiedValue) {this. Modified = dbModifiedValue;} if (this. Created! = OriginalCreatedValue) {databaseItem ["Created"] = this. Created;} else if (this. Created = originalCreatedValue & this. Created! = DbCreatedValue) {this. Created = dbCreatedValue;} if (this. CreatedBy! = OriginalCreatedByValue) {databaseItem ["Author"] = this. CreatedBy;} else if (this. CreatedBy = originalCreatedByValue & this. CreatedBy! = DbCreatedByValue) {this. CreatedBy = dbCreatedByValue;} if (this. ModifiedBy! = OriginalModifiedByValue) {databaseItem ["Editor"] = this. ModifiedBy;} else if (this. ModifiedBy = originalModifiedByValue & this. ModifiedBy! = DbModifiedByValue) {this. modifiedBy = dbModifiedByValue ;}} public DateTime Modified {get; set;} public DateTime Created {get; set;} public string CreatedBy {get; set;} public string ModifiedBy {get; set ;}}

For more information about the ICustomMapping interface, see the following MSDN articles: ICustomMapping members and RefreshMode enumeration.

After adding this file to our project, we can use the creator, creation time, modifier, and modification time when querying:

StringBuilder output = new StringBuilder (); using (SiteEntitiesDataContext model = new SiteEntitiesDataContext ("http://moss.contoso.com/sites/Lab01") {string dateStr = ("2012/8/6 "). toString (CultureInfo. invariantCulture); DateTime convertedDate = DateTime. specifyKind (DateTime. parse (dateStr), DateTimeKind. utc); foreach (Manager project manager in model. manager. where (x => x. created> convertedDate) {output. appendLine (manager. title + "" + manager. name) ;}} Console. writeLine (output. toString ());

Use the original Manager list to add the "Creation Time" column in the current view:

The preceding Code queries all list items created. The result is as follows:

Note that the author and modifier columns are User-type. Directly output these strings may contain a lot of information you don't need. A simple method to parse the string is to create a SPFieldUserValue, with the current SPWeb and the string as the parameter. Then, we can extract the actual SPUser information from SPFieldUserValue. User.
For example:

Using (SPSite site = new SPSite ("http://moss.contoso.com/sites/Lab01") {using (SPWeb = site. openWeb () {using (SiteEntitiesDataContext ctx = new SiteEntitiesDataContext ("http://moss.contoso.com/sites/Lab01") {var result = ctx. manager. where (m => m. country = Country. USA); foreach (Manager project manager in result) {SPFieldUserValue t = new SPFieldUserValue (web, manager. createdBy); Console. writeLine (manager. title + "" + manager. name + "-" + t. user. name + "-" + manager. modified. toShortDateString ());}}}}

Output result:

Hope this code is helpful to you, Enjoy SharePoint!

 

References

LINQ To SharePoint: Working with Created, CreatedBy, Modified and ModifiedBy