Click here to Skip to main content
15,792,870 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I am trying to develop an API which will be taking few movie names as part of ViewModel and will be setting respective movie IDs in DTO.

The table representation for TBL_Movies (For example I have used only 5 data but in real there are quite few):

ID	MovieName
1	Indiana Jones
2	John Wick
3	James Bond
4	Wolverine

The table representation for TBL_Transact:

Tab_ID	MovieID1(int)	MovieID2(int)	MovieID3(int)	MovieID4(int)	

The ViewModel structure:
	Tab_ID: int,
	MovieName1: "John Wick",
	MovieName2: "Wolverine",
	MovieName3: "Cast Away",
	MovieName4: "A-Team" // this doesn't exist in TBL_Movies and so it will return exception to the client

The above ViewModel will be passed to the API PUT endpoint and now I need to fetch the respective MovieIDs for respective ViewModel fields and store them in DTO.

The DTO (MovieDetailUpdateDto) structure:
	Tab_ID: int,
	MovieName1: string,
	MovieName2: string,
	MovieName3: string,
	MovieName4: string,
	MovieID1: int,
	MovieID2: int,
	MovieID3: int,
	MovieID4: int

So, DTO will be storing Movie ID in MovieID1 as the ID for the movie mentioned in the field MovieName1 and so on.

For example:
DTO.MovieID1 will be storing 2 (as "John Wick" is in MovieName1).

What I have tried:

I don't want to call the database for 5 or more times to get the IDs. So, I thought of using SQL IN operator and since am using EF-LINQ, so did something like this:
var movieDetailUpdateDto = _mapper.Map<MovieDetailUpdateDto>(ViewModel);

var customMovieValuesList = typeof(MovieDetailUpdateDto).GetProperties().Where(x => x.Name.ToLower().Contains("moviename")).ToList().Select(x => x.GetValue(movieDetailUpdateDto));

The above statement is giving me a list of the movie names but am unable to understand for which field the specific name is.

For example:
If the value "John Wick", in the list, is for the field MovieName1 or MovieName2 or any other MovieName fields.

So, I think using Dictionary I can be able to store the fieldname and its values. But, not sure how it will work or whether it will work or not?

Secondly, will it be possible to use "customMovieValuesList" in SQL IN operator using LINQ?

Thirdly, how to assign those MovieIDs back to DTO into respective fields using LINQ?
Updated 24-Jul-23 23:04pm
Graeme_Grant 24-Jul-23 10:13am    
Why make it so difficult for yourself?
Member 11072126 25-Jul-23 2:43am    
@Graeme_Grant - This is a legacy application. Not allowed to change the table design. But, need these IDs to be set in respective fields. Kindly, suggest some way out.
Graeme_Grant 25-Jul-23 4:13am    
I stubbled across your comment as you did not reply to my post, so I was never alerted.

As mentioned by Richard, use the KISS methodology and lose the reflection. Legacy does not make it better, it is one of the reasons why you are here asking for help!

1 solution

You'll want to start by getting rid of that reflection!

Add a couple of methods to your DTO:
public IEnumerable<string> AllMovieNames()
    if (!string.IsNullOrEmpty(MovieName1)) yield return MovieName1;
    if (!string.IsNullOrEmpty(MovieName2)) yield return MovieName2;
    if (!string.IsNullOrEmpty(MovieName3)) yield return MovieName3;
    if (!string.IsNullOrEmpty(MovieName4)) yield return MovieName4;
    if (!string.IsNullOrEmpty(MovieName5)) yield return MovieName5;

private static int GetAndValidateId(IReadOnlyDictionary<string, int> map, string key)
    if (string.IsNullOrEmpty(key)) return 0;
    if (map.TryGetValue(key, out int id)) return id;
    throw new InvalidOperationException($"Movie '{key}' does not exist.");

public void UpdateMovieIds(IReadOnlyDictionary<string, int> map)
    MovieId1 = GetAndValidateId(map, MovieName1);
    MovieId2 = GetAndValidateId(map, MovieName2);
    MovieId3 = GetAndValidateId(map, MovieName3);
    MovieId4 = GetAndValidateId(map, MovieName4);
    MovieId5 = GetAndValidateId(map, MovieName5);
Then in your mapping code:
var movieDetailUpdateDto = _mapper.Map<MovieDetailUpdateDto>(ViewModel);

var allMovieNames = movieDetailUpdateDto.AllMovieNames().ToList();

var map = await yourDbContext.Movies
    .Where(m => allMovieNames.Contains(m.MovieName))
    .ToDictionaryAsync(m => m.MovieName, m => m.Id);

Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900