EF Core FreeTextTable Can't Get Rank-CodePudding

I am running the following query: (see https://learn.microsoft.com/en-us/sql/relational-databases/search/query-with-full-text-search?view=sql-server-ver16)

SELECT KEY_TBL.RANK, FT_TBL.* 
FROM dbo.Forums AS FT_TBL 
   INNER JOIN FREETEXTTABLE(dbo.Forums, Title,'{searchTerm}') AS KEY_TBL
      ON FT_TBL.Id = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC

This works great when I run it in SQL Server Management Studio, but when I run it in my API, I do not get the rank. It is always 0.

Here is my model:

public class Forum : BaseModel
    {
        public ApplicationUser? ForumAuthor { get; set; }
        public string ForumAuthorId { get; set; } = "";
        
        [Required (AllowEmptyStrings = false,ErrorMessage = "A Title is required")]
        public string? Title { get; set; }
        public List<Post>? Posts { get; set; }
        [NotMapped]
        public int RANK { get; set; }
    }

Notice that RANK has a [NotMapped] attribute. I did that because I don't have a RANK field on the Forums table.

So I decided to create a ForumViewModel that has a RANK field without the [NotMapped] attribute.

Here is my query:

List<ForumViewModel> forums = await _context.Forums.FromSqlRaw(forumSql).Select(x => new ForumViewModel()
            {
                ForumAuthor = x.ForumAuthor,
                ForumAuthorId = x.ForumAuthorId,
                Posts = x.Posts,
                RANK = x.RANK,
                Title = x.Title,

            }).ToListAsync();

The result is the same. RANK = 0.

If I remove the [NotMapped] attribute, I get the rank with a valid number greater than 0. So I believe the [NotMapped] attribute is the problem.

The rank is returned by the FREETEXTTABLE. So, I am using DbContext to query my Forums table, joined to FREETEXTTABLE. This table returns the rank of the search term against all text in the title field of the Forums table. Please see the link I put in the beginning of this post.

How can I get the Rank without having to needlessly add the Rank field to my Forum table.

Edit: Solved I figured it out. There is a data annotation for this situation:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public int RANK { get; set; }

CodePudding user response：

Solved! I figured it out. There is a data annotation for this situation: Generated Columns

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public int RANK { get; set; }

CodePudding user response：

Here is how to get rank in linq. You need to orderbydescending and then group quantities. See below where I created a dictionary of the values.

           int[] quantities = { 49, 49, 41, 30, 17, 35, 25, 24, 14, 12 };

           Dictionary<int, int> rankDictionary = quantities
                .OrderByDescending(x => x)
                //skip zero
                .Select((x, i) => new { quantity = x, index = i   1 })
                .GroupBy(x => x.quantity, y => y.index)
                .ToDictionary(x => x.Key, y => y.Min(z => z));